SQL Server Integration Services Design Patterns (eBook)

Tim Mitchell is a business intelligence consultant, database developer, speaker, and trainer. He has been working with SQL Server for more than eight years, working primarily in business intelligence, ETL/SSIS, database development, and reporting. He has earned a number of industry certifications, holds a bachelor's degree in computer science from Texas A&M University at Commerce, and is a Microsoft SQL Server "Most Valuable Professional". Tim is a business intelligence consultant for Artis Consulting in the Dallas, Texas area. As an active member of the community, Tim has spoken at venues including numerous SQL Saturday events, Houston Tech Fest, and various user groups and PASS virtual chapters. He is a board member and speaker at the North Texas SQL Server User Group in Dallas, serves as the co-chair of the PASS BI Virtual Chapter, and is an active volunteer for PASS. Tim is an author and forum contributor on SQLServerCentral.com and has published dozens of SQL Server training videos on SQLShare.com. You can visit his website and blog at TimMitchell.net or follow him on Twitter @Tim_Mitchell.

Contents at a Glance 3
Contents 438
First-Edition Foreword 448
About the Authors 449
About the Technical Reviewer 451
Chapter 1: Metadata Collection 5
About SQL Server Data Tools 5
A Peek at the Final Product 5
SQL Server Metadatacatalog 7
sys.dm_os_performance_counters 7
sys.dm_db_index_usage_stats 7
sys.dm_os_sys_info 7
sys.tables 7
sys.indexes 7
sys.partitions 8
sys.allocation_units 8
Setting Up the Central Repository 8
The Iterative Framework 10
Metadata Collection 18
Summary 30
Chapter 2: Execution Patterns 31
Building the Demonstration SSIS Package 31
Debug Execution 32
Command-Line Execution 33
Execute Package Utility 34
The SQL Server 2014 Integration Services Service 34
Integration Services Catalogs 34
Integration Server Catalog Stored Procedures 35
Adding a Data Tap 39
Testing the Data Tap Procedure 43
Creating a Custom Execution Framework 45
Scheduling SSIS Package Execution 57
Scheduling an SSIS Package 57
Scheduling a File System Package 58
Running SQL Server Agent Jobs with the Custom Execution Framework 59
Running the Custom Execution Framework with SQL Server Agent 60
Execute Package Task 61
Execution from Managed Code 62
The Demo Application 62
The frmMain Form 63
Conclusion 74
Chapter 3: Scripting Patterns 75
The Toolset 75
Should I Use Script? 76
The Script Editor 76
Project Explorer 77
Full .NET Runtime 78
Compiler 78
The Script Task 79
The Script Component 81
Script Maintenance Patterns 82
Code Reuse 82
Copy/Paste 82
External Assemblies 82
Custom Tasks/Components 83
Source Control 83
Scripting Design Patterns 83
Connection Managers and Scripting 84
Using Connection Managers in the Script Task 84
Using Connection Managers in the Script Component 85
Variables 86
Variable Visibility 87
Variable Syntax in Code 87
Variable Data Types 88
Naming Patterns 89
Conclusion 89
Chapter 4: SQL Server Source Patterns 90
Setting Up a Source 90
Selecting a SQL Server Connection Manager and Provider 91
ADO.NET 92
ODBC 92
OLE DB 94
Creating a SQL Server Source Component 95
Writing a SQL Server Source Component Query 98
ADO.NET Data Access 98
OLE DB Data Access 99
Waste Not, Want Not 100
Data Translations 100
Source Assistant 100
Summary 102
Chapter 5: Data Correction with Data Quality Services 103
Overview of Data Quality Services 103
Using the Data Quality Client 104
Knowledge Base Management 105
Data Quality Projects 106
Administration 108
Using the Default Knowledge Base 108
Online Reference Data Services 109
Using DQS with SSIS 110
DQS Cleansing Transform 110
DQS Extensions on CodePlex 115
Cleansing Data in the Data Flow 116
Handling the Output of the DQS Cleansing Transform 116
Performance Considerations 119
Parallel Processing 119
Tracking Which Rows Have Been Cleansed 119
Filtering Rows with the Lookup Transform 120
Approving and Importing Cleansing Rules 123
Conclusion 125
Chapter 6: DB2 Source Patterns 126
DB2 Database Family 126
Selecting a DB2 Provider 127
Find the Database Version 127
Pick Provider Vendor 128
Connecting to a DB2 Database 128
Querying the DB2 Database 131
DB2 Source Component Parameters 132
DB2 Source Component Dynamic Queries 133
Summary 134
Chapter 7: Flat File Source Patterns 135
Flat File Sources 135
Moving to SSIS! 136
Strong-Typing the Data 138
Introducing a Data-Staging Pattern 140
Variable-Length Rows 143
Reading into a Data Flow 144
Splitting Record Types 145
Terminating the Streams 146
Header and Footer Rows 147
Consuming a Footer Row 148
Consuming a Header Row 150
Producing a Footer Row 152
Producing a Header Row 159
The Archive File Pattern 163
Summary 169
Chapter 8: Loading a PDW Region in APS 170
Massively Parallel Processing 170
APS Appliance Overview 171
Hardware Architecture 171
Software Architecture 172
Shared-Nothing Architecture 174
Clustered Columnstore Indexes 174
Loading Data 175
DWLoader vs. Integration Services 175
ETL vs. ELT 176
Data Import Pattern for PDW 177
Prerequisites 177
Preparing the Data 178
Package Overview 180
The Data Source 180
The Data Transformation 182
The Data Destination 183
Multithreading 188
Limitations 189
Summary 190
Chapter 9: XML Patterns 191
Using the XML Source 191
Dealing with Multiple Outputs 192
Making Things Easier with XSLT 198
Using a Script Component 201
Configuring the Script Component 201
Processing XML with XmlSerializer 207
Processing XML with XmlReader and LINQ to XML 208
Conclusion 210
Chapter 10: Expression Language Patterns 211
Getting to Know the Expression Language 211
What Is the Expression Language? 211
Why Use Expressions? 212
Language Essentials 213
Limitations 213
Putting the Expression Language to Work 214
Package Expressions 214
Variable Expressions 215
Connection Managers 215
Project-Level Connection Managers 217
Control Flow 217
Conditional Execution Through Expressions and Constraints 217
Task-Level Expressions 220
Data Flow Expressions 220
Data Cleansing 220
Branching 221
Application of Business Rules 223
Conclusion 224
Chapter 11: Data Warehouse Patterns 225
Incremental Loads 225
What Is an Incremental Load? 225
Why Incremental Loads? 226
The Slowly Changing Dimension 226
Incremental Loads of Fact Data 226
Incremental Loads in SSIS 226
Native SSIS Components 227
The Moving Parts 227
Typical Uses 228
Lookup Caching Options 229
Table Cache 229
Cache Transformation and Cache Connection Manager 229
Load Staging 230
The Slowly Changing Dimension Wizard 230
The MERGE Statement 232
A Little Background 232
MERGE in Action 233
Auditing with MERGE 235
Change Data Capture (CDC) 235
CDC in Integration Services 235
Change Detection in General 236
Checksum-Based Detection 236
Detection via Hashbytes 237
Brute Force Detection 237
Historical Load 237
Incremental Load 238
Typical Uses 240
Data Error s 240
Simple Errors 240
Missing Data 241
Use the Unknown Member 241
Add the Missing Dimension Member 242
Triage the Lookup Failures 243
Coding to Allow Errors 244
Fail Package on Error 245
Unhandled Errors 246
Data Warehouse ETL Workflow 246
Dividing Up the Work 246
One Package = One Unit of Work 247
Conclusion 248
Chapter 12: OData Source 249
Understanding the OData Protocol 249
Data Type Mappings 250
Query Options 251
Configuring the OData Connection Manager 252
Enabling Microsoft Online Services Authentication 252
Configuring the Source Component 254
Overriding Data Types 257
Conclusion 258
Chapter 13: Slowly Changing Dimensions 259
The Slowly Changing Dimension Transform 259
Running the Wizard 260
Using the Transformations 265
Optimizing Performance 266
The Slowly Changing Dimension Transform 267
OLE DB Command Transforms 267
OLE DB Destination 267
Third-Party SCD Components 267
Merge Pattern 268
Handling Type 1 Changes 269
Handling Type 2 Changes 270
Conclusion 270
Chapter 14: Loading the Cloud 272
Interacting with the Cloud 272
Incremental Loads to Azure SQL Database 273
Change Detection 273
New Rows (Only) 273
Building the Cloud Loader 274
Conclusion 277
Chapter 15: Logging and Reporting Patterns 278
Package Logging and Reporting 278
Setting Up Package Logging 278
Reporting on Package Logging 279
Design Pattern: Package Executions 280
Catalog Logging and Reporting 280
Setting Up Catalog Logging 280
Catalog Tables 282
Changing Logging Levels After the Fact 283
Design Patterns 284
Changing the Logging Level 284
Using the Existing Reports 286
Creating New Reports 287
Summary 288
Chapter 16: Parent-Child Patterns 289
Master Package Pattern 289
Assign the Child Package 290
Configure Parameter Binding 291
Dynamic Child Package Pattern 292
Child-to-Parent Variable Pattern 298
Conclusion 299
Chapter 17: Configuration 300
Parameters 300
Configuring Your Package Using Parameters 302
Using the Parametrize Dialog 304
Creating Visual Studio Configurations 305
Specifying Entry-Point Packages 307
Connection Managers 308
Parameter Configuration on the Server 308
Default Configuration 309
Server Environments 310
Default Parameter Values Using T-SQL 312
Package Execution Through the SSIS Catalog 312
Parameters with DTEXEC 315
Projects on the File System 315
Projects in the SSIS Catalog 316
Dynamic Configurations 317
Configuring from a Database Table 318
Creating the Database Table 318
Retrieving Configuration Values with an Execute SQL Task 318
Setting Values Using a Script Task 321
Dynamic Package Executions 322
Conclusion 324
Chapter 18: Deployment 325
Project Deployment Model 325
SSIS Catalog 326
Deployment Methods 328
Deployment from the Command Line 329
Deployment Using Custom Code 330
Deployment Using PowerShell 331
Deployment Using SQL 332
Package Deployment Model 333
Conclusion 335
Chapter 19: Business Intelligence Markup Language 336
A Brief History of Business Intelligence Markup Language 336
Building Your First Biml File 337
Building a Basic Incremental Load SSIS Package 340
Creating Databases and Tables 340
Adding Metadata 342
Specifying a Data Flow Task 343
Adding Transforms 343
Testing the Biml 349
Using Biml as an SSIS Design Patterns Engine 353
Time for a Test 360
Conclusion 361
Chapter 20: Biml and SSIS Frameworks 362
Using Biml with an SSIS Framework 362
Adding SSIS Package Metadata to the Framework 362
Executing the Biml File 367
Generating the SSIS Command-Line 368
Summarizing 369
Chapter 21:Evolution of an SSIS Framework 370
Starting in the Middle 370
Introducing SSIS Applications 380
A Note About Relationships 382
Retrieving SSIS Applications in T-SQL 385
Retrieving SSIS Applications in SSIS 389
Monitoring Execution 392
Building Application Instance Logging 392
Building Package Instance Logging 399
Building Error Logging 403
Reporting Execution Metrics 413
Conclusion 427
Index 428