Home Page Icon
Home Page
Table of Contents for
Cover
Close
Cover
by Michelle Ufford, Jessica Moss, Matt Masson, Tim Mitchell, Andy Leonard
SQL Server Integration Services Design Patterns, Second Edition
Cover
Title
Copyright
Contents at a Glance
Contents
First-Edition Foreword
About the Authors
About the Technical Reviewer
Chapter 1: Metadata Collection
About SQL Server Data Tools
A Peek at the Final Product
SQL Server Metadatacatalog
sys.dm_os_performance_counters
sys.dm_db_index_usage_stats
sys.dm_os_sys_info
sys.tables
sys.indexes
sys.partitions
sys.allocation_units
Setting Up the Central Repository
The Iterative Framework
Metadata Collection
Summary
Chapter 2: Execution Patterns
Building the Demonstration SSIS Package
Debug Execution
Command-Line Execution
Execute Package Utility
The SQL Server 2014 Integration Services Service
Integration Services Catalogs
Integration Server Catalog Stored Procedures
Scheduling SSIS Package Execution
Scheduling an SSIS Package
Scheduling a File System Package
Running SQL Server Agent Jobs with the Custom Execution Framework
Running the Custom Execution Framework with SQL Server Agent
Execute Package Task
Execution from Managed Code
The Demo Application
The frmMain Form
Conclusion
Chapter 3: Scripting Patterns
The Toolset
Should I Use Script?
The Script Editor
The Script Task
The Script Component
Script Maintenance Patterns
Code Reuse
Source Control
Scripting Design Patterns
Connection Managers and Scripting
Variables
Naming Patterns
Conclusion
Chapter 4: SQL Server Source Patterns
Setting Up a Source
Selecting a SQL Server Connection Manager and Provider
ADO.NET
ODBC
OLE DB
Creating a SQL Server Source Component
Writing a SQL Server Source Component Query
ADO.NET Data Access
OLE DB Data Access
Waste Not, Want Not
Data Translations
Source Assistant
Summary
Chapter 5: Data Correction with Data Quality Services
Overview of Data Quality Services
Using the Data Quality Client
Using DQS with SSIS
DQS Cleansing Transform
DQS Extensions on CodePlex
Cleansing Data in the Data Flow
Handling the Output of the DQS Cleansing Transform
Performance Considerations
Approving and Importing Cleansing Rules
Conclusion
Chapter 6: DB2 Source Patterns
DB2 Database Family
Selecting a DB2 Provider
Find the Database Version
Pick Provider Vendor
Connecting to a DB2 Database
Querying the DB2 Database
DB2 Source Component Parameters
DB2 Source Component Dynamic Queries
Summary
Chapter 7: Flat File Source Patterns
Flat File Sources
Moving to SSIS!
Strong-Typing the Data
Introducing a Data-Staging Pattern
Variable-Length Rows
Reading into a Data Flow
Splitting Record Types
Terminating the Streams
Header and Footer Rows
Consuming a Footer Row
Consuming a Header Row
Producing a Footer Row
Producing a Header Row
The Archive File Pattern
Summary
Chapter 8: Loading a PDW Region in APS
Massively Parallel Processing
APS Appliance Overview
Hardware Architecture
Software Architecture
Shared-Nothing Architecture
Clustered Columnstore Indexes
Loading Data
DWLoader vs. Integration Services
ETL vs. ELT
Data Import Pattern for PDW
Prerequisites
Preparing the Data
Package Overview
The Data Source
The Data Transformation
The Data Destination
Multithreading
Limitations
Summary
Chapter 9: XML Patterns
Using the XML Source
Dealing with Multiple Outputs
Making Things Easier with XSLT
Using a Script Component
Configuring the Script Component
Processing XML with XmlSerializer
Processing XML with XmlReader and LINQ to XML
Conclusion
Chapter 10: Expression Language Patterns
Getting to Know the Expression Language
What Is the Expression Language?
Why Use Expressions?
Language Essentials
Limitations
Putting the Expression Language to Work
Package Expressions
Variable Expressions
Connection Managers
Project-Level Connection Managers
Control Flow
Data Flow Expressions
Conclusion
Chapter 11: Data Warehouse Patterns
Incremental Loads
What Is an Incremental Load?
Why Incremental Loads?
The Slowly Changing Dimension
Incremental Loads of Fact Data
Incremental Loads in SSIS
Native SSIS Components
The Slowly Changing Dimension Wizard
The MERGE Statement
Change Data Capture (CDC)
Data Errors
Simple Errors
Missing Data
Coding to Allow Errors
Data Warehouse ETL Workflow
Dividing Up the Work
One Package = One Unit of Work
Conclusion
Chapter 12: OData Source
Understanding the OData Protocol
Data Type Mappings
Query Options
Configuring the OData Connection Manager
Enabling Microsoft Online Services Authentication
Configuring the Source Component
Overriding Data Types
Conclusion
Chapter 13: Slowly Changing Dimensions
The Slowly Changing Dimension Transform
Running the Wizard
Using the Transformations
Optimizing Performance
Third-Party SCD Components
Merge Pattern
Handling Type 1 Changes
Handling Type 2 Changes
Conclusion
Chapter 14: Loading the Cloud
Interacting with the Cloud
Incremental Loads to Azure SQL Database
Change Detection
New Rows (Only)
Building the Cloud Loader
Conclusion
Chapter 15: Logging and Reporting Patterns
Package Logging and Reporting
Setting Up Package Logging
Reporting on Package Logging
Design Pattern: Package Executions
Catalog Logging and Reporting
Setting Up Catalog Logging
Catalog Tables
Changing Logging Levels After the Fact
Design Patterns
Changing the Logging Level
Using the Existing Reports
Creating New Reports
Summary
Chapter 16: Parent-Child Patterns
Master Package Pattern
Assign the Child Package
Configure Parameter Binding
Dynamic Child Package Pattern
Child-to-Parent Variable Pattern
Conclusion
Chapter 17: Configuration
Parameters
Configuring Your Package Using Parameters
Using the Parametrize Dialog
Creating Visual Studio Configurations
Specifying Entry-Point Packages
Connection Managers
Parameter Configuration on the Server
Default Configuration
Server Environments
Default Parameter Values Using T-SQL
Package Execution Through the SSIS Catalog
Parameters with DTEXEC
Projects on the File System
Projects in the SSIS Catalog
Dynamic Configurations
Configuring from a Database Table
Setting Values Using a Script Task
Dynamic Package Executions
Conclusion
Chapter 18: Deployment
Project Deployment Model
SSIS Catalog
Deployment Methods
Deployment from the Command Line
Deployment Using Custom Code
Deployment Using PowerShell
Deployment Using SQL
Package Deployment Model
Conclusion
Chapter 19: Business Intelligence Markup Language
A Brief History of Business Intelligence Markup Language
Building Your First Biml File
Building a Basic Incremental Load SSIS Package
Creating Databases and Tables
Adding Metadata
Specifying a Data Flow Task
Adding Transforms
Testing the Biml
Using Biml as an SSIS Design Patterns Engine
Time for a Test
Conclusion
Chapter 20: Biml and SSIS Frameworks
Using Biml with an SSIS Framework
Adding SSIS Package Metadata to the Framework
Executing the Biml File
Generating the SSIS Command-Line
Summarizing
Appendix A: Evolution of an SSIS Framework
Starting in the Middle
Introducing SSIS Applications
A Note About Relationships
Retrieving SSIS Applications in T-SQL
Retrieving SSIS Applications in SSIS
Monitoring Execution
Building Application Instance Logging
Building Package Instance Logging
Building Error Logging
Reporting Execution Metrics
Conclusion
Index
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset