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