Contents
Chapter 1: Metadata Collection
Introducing SQL Server Data Tools
Setting Up the Central Repository
Building the Demonstration SSIS Package
The SQL Server 2012 Integration Services Service
Integration Server Catalog Stored Procedures
Scheduling SSIS Package Execution
Scheduling a File System Package
Running SQLAgent Jobs with the Custom Execution Framework
Running the Custom Execution Framework with SQL Agent
Connection Managers and Scripting
Using Connection Managers in the Script Task
Using Connection Managers in the Script Component
Chapter 4: SQL Server Source Patterns
Selecting a SQL Server Connection Manager and Provider
Creating a SQL Server Source Component
Writing a SQL Server Source Component Query
Chapter 5: Data Cleansing with Data Quality Services
Overview of Data Quality Services
Cleansing Data in the Data Flow
Handling the Output of the DQS Cleansing Transform
Approving and Importing Cleansing Rules
Chapter 6: DB2 Source Patterns
DB2 Source Component Parameters
DB2 Source Component Dynamic Queries
Chapter 7: Flat File Source Patterns
Introducing a Data-Staging Pattern
Chapter 8: Parallel Data Warehouse Patterns
DWLoader vs. Integration Services
Installing the PDW Destination Adapter
Making Things Easier with XSLT
Configuring the Script Component
Processing XML with XmlSerializer
Processing XML with XmlReader and LINQ to XML
Chapter 10: Expression Language Patterns
Getting to Know the Expression Language
Putting Expression Language to Work
Project-level Connection Managers
Chapter 11: Data Warehouse Patterns
Incremental Loads of Fact Data
The Slowly Changing Dimension Wizard
One Package = One Unit of Work
Chapter 13: Slowly Changing Dimensions
Slowly Changing Dimension Transform
Incremental Loads to SQL Azure
Chapter 15: Logging and Reporting Patterns
Design Pattern: Package Executions
Changing Logging Levels After the Fact
Chapter 16: Parent-Child Patterns
Child to Parent Variable Pattern
Chapter 17: Business Intelligence Markup Language
A Brief History of Business Intelligence Markup Language
Building a Basic Incremental Load SSIS Package
Using Biml as an SSIS Design Patterns Engine
Configuring Your Package Using Parameters
Creating Visual Studio Configurations
Specifying Entry Point Packages
Parameter Configuration on the Server
Default Parameter Values Using T-SQL
Package Execution through the SSIS Catalog
Configuring from a Database Table
Setting Values using a Script Task
Deployment from the Command Line
Chapter 20: Estimating ETL Projects
It’s difficult because it requires—communication
It’s difficult because it requires guesswork
It’s difficult because it relies on technology
The secret to estimating ETL project timelines is
Don’t forget the little things
Know the personalities involved
Learn to do it right by doing it wrong
When the timeline slips, communicate early and often
Appendix A: Evolution of an SSIS Framework
Retrieving SSIS Applications in T-SQL
Retrieving SSIS Applications in SSIS
Building Application Instance Logging