Mitchell / Masson / Leonard SQL Server Integration Services Design Patterns

2. Auflage 2014
ISBN: 978-1-4842-0082-7
Verlag: APRESS
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 451 Seiten, eBook

SQL Server Integration Services Design Patterns is newly-revised for SQL Server 2014, and is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book help to solve common problems encountered when developing data integration solutions. The patterns and solution examples in the book increase your efficiency as an SSIS developer, because you do not have to design and code from scratch with each new problem you face. The book's team of expert authors take you through numerous design patterns that you'll soon be using every day, providing the thought process and technical details needed to support their solutions. SQL Server Integration Services Design Patterns goes beyond the surface of the immediate problems to be solved, delving into why particular problems should be solved in certain ways. You'll learn more about SSIS as a result, and you'll learn by practical example. Where appropriate, the book provides examples of alternative patterns and discusses when and where they should be used. Highlights of the book include sections on ETL Instrumentation, SSIS Frameworks, Business Intelligence Markup Language, and Dependency Services.Takes you through solutions to common data integration challengesProvides examples involving Business Intelligence Markup LanguageTeaches SSIS using practical examples

Zielgruppe

Popular/general

Autoren/Hrsg.

Mitchell, Tim

Masson, Matt

Leonard, Andy

Moss, Jessica

Ufford, Michelle

Inhaltsverzeichnis

1;Contents at a Glance;3
2;Contents;438
3;First-Edition Foreword;448
4;About the Authors;449
5;About the Technical Reviewer;451
6;Chapter 1: Metadata Collection;5
6.1;About SQL Server Data Tools;5
6.2;A Peek at the Final Product;5
6.3;SQL Server Metadatacatalog;7
6.3.1;sys.dm_os_performance_counters;7
6.3.2;sys.dm_db_index_usage_stats;7
6.3.3;sys.dm_os_sys_info;7
6.3.4;sys.tables;7
6.3.5;sys.indexes;7
6.3.6;sys.partitions;8
6.3.7;sys.allocation_units;8
6.4;Setting Up the Central Repository;8
6.5;The Iterative Framework;10
6.6;Metadata Collection;18
6.7;Summary;30
7;Chapter 2: Execution Patterns;31
7.1;Building the Demonstration SSIS Package;31
7.1.1;Debug Execution;32
7.1.2;Command-Line Execution;33
7.1.3;Execute Package Utility;34
7.2;The SQL Server 2014 Integration Services Service;34
7.2.1;Integration Services Catalogs;34
7.2.2;Integration Server Catalog Stored Procedures;35
7.2.2.1;Adding a Data Tap;39
7.2.2.2;Testing the Data Tap Procedure;43
7.2.2.3;Creating a Custom Execution Framework;45
7.3;Scheduling SSIS Package Execution;57
7.3.1;Scheduling an SSIS Package;57
7.3.2;Scheduling a File System Package;58
7.3.3;Running SQL Server Agent Jobs with the Custom Execution Framework;59
7.3.4;Running the Custom Execution Framework with SQL Server Agent;60
7.3.5;Execute Package Task;61
7.4;Execution from Managed Code;62
7.4.1;The Demo Application;62
7.4.2;The frmMain Form;63
7.5;Conclusion;74
8;Chapter 3: Scripting Patterns;75
8.1;The Toolset;75
8.1.1;Should I Use Script?;76
8.1.2;The Script Editor;76
8.1.2.1;Project Explorer;77
8.1.2.2;Full .NET Runtime;78
8.1.2.3;Compiler;78
8.1.3;The Script Task;79
8.1.4;The Script Component;81
8.2;Script Maintenance Patterns;82
8.2.1;Code Reuse;82
8.2.1.1;Copy/Paste;82
8.2.1.2;External Assemblies;82
8.2.1.3;Custom Tasks/Components;83
8.2.2;Source Control;83
8.3;Scripting Design Patterns;83
8.3.1;Connection Managers and Scripting;84
8.3.1.1;Using Connection Managers in the Script Task;84
8.3.1.2;Using Connection Managers in the Script Component;85
8.3.2;Variables;86
8.3.2.1;Variable Visibility;87
8.3.2.2;Variable Syntax in Code;87
8.3.2.3;Variable Data Types;88
8.3.3;Naming Patterns;89
8.4;Conclusion;89
9;Chapter 4: SQL Server Source Patterns;90
9.1;Setting Up a Source;90
9.2;Selecting a SQL Server Connection Manager and Provider;91
9.2.1;ADO.NET;92
9.2.2;ODBC;92
9.2.3;OLE DB;94
9.3;Creating a SQL Server Source Component;95
9.4;Writing a SQL Server Source Component Query;98
9.4.1;ADO.NET Data Access;98
9.4.2;OLE DB Data Access;99
9.4.3;Waste Not, Want Not;100
9.4.4;Data Translations;100
9.5;Source Assistant;100
9.6;Summary;102
10;Chapter 5: Data Correction with Data Quality Services;103
10.1;Overview of Data Quality Services;103
10.1.1;Using the Data Quality Client;104
10.1.1.1;Knowledge Base Management;105
10.1.1.2;Data Quality Projects;106
10.1.1.3;Administration;108
10.1.1.4;Using the Default Knowledge Base;108
10.1.1.5;Online Reference Data Services;109
10.2;Using DQS with SSIS;110
10.2.1;DQS Cleansing Transform;110
10.2.2;DQS Extensions on CodePlex;115
10.3;Cleansing Data in the Data Flow;116
10.3.1;Handling the Output of the DQS Cleansing Transform;116
10.3.2;Performance Considerations;119
10.3.2.1;Parallel Processing;119
10.3.2.2;Tracking Which Rows Have Been Cleansed;119
10.3.2.3;Filtering Rows with the Lookup Transform;120
10.4;Approving and Importing Cleansing Rules;123
10.5;Conclusion;125
11;Chapter 6: DB2 Source Patterns;126
11.1;DB2 Database Family;126
11.2;Selecting a DB2 Provider;127
11.2.1;Find the Database Version;127
11.2.2;Pick Provider Vendor;128
11.3;Connecting to a DB2 Database;128
11.4;Querying the DB2 Database;131
11.4.1;DB2 Source Component Parameters;132
11.4.2;DB2 Source Component Dynamic Queries;133
11.5;Summary;134
12;Chapter 7: Flat File Source Patterns;135
12.1;Flat File Sources;135
12.1.1;Moving to SSIS!;136
12.1.2;Strong-Typing the Data;138
12.1.3;Introducing a Data-Staging Pattern;140
12.2;Variable-Length Rows;143
12.2.1;Reading into a Data Flow;144
12.2.2;Splitting Record Types;145
12.2.3;Terminating the Streams;146
12.3;Header and Footer Rows;147
12.3.1;Consuming a Footer Row;148
12.3.2;Consuming a Header Row;150
12.3.3;Producing a Footer Row;152
12.3.4;Producing a Header Row;159
12.4;The Archive File Pattern;163
12.5;Summary;169
13;Chapter 8: Loading a PDW Region in APS;170
13.1;Massively Parallel Processing;170
13.2;APS Appliance Overview;171
13.2.1;Hardware Architecture;171
13.2.2;Software Architecture;172
13.2.3;Shared-Nothing Architecture;174
13.2.4;Clustered Columnstore Indexes;174
13.3;Loading Data;175
13.3.1;DWLoader vs. Integration Services;175
13.3.2;ETL vs. ELT;176
13.4;Data Import Pattern for PDW;177
13.4.1;Prerequisites;177
13.4.2;Preparing the Data;178
13.4.3;Package Overview;180
13.4.4;The Data Source;180
13.4.5;The Data Transformation;182
13.4.6;The Data Destination;183
13.4.7;Multithreading;188
13.5;Limitations;189
13.6;Summary;190
14;Chapter 9: XML Patterns;191
14.1;Using the XML Source;191
14.1.1;Dealing with Multiple Outputs;192
14.1.2;Making Things Easier with XSLT;198
14.2;Using a Script Component;201
14.2.1;Configuring the Script Component;201
14.2.2;Processing XML with XmlSerializer;207
14.2.3;Processing XML with XmlReader and LINQ to XML;208
14.3;Conclusion;210
15;Chapter 10: Expression Language Patterns;211
15.1;Getting to Know the Expression Language;211
15.1.1;What Is the Expression Language?;211
15.1.2;Why Use Expressions?;212
15.1.3;Language Essentials;213
15.1.4;Limitations;213
15.2;Putting the Expression Language to Work;214
15.2.1;Package Expressions;214
15.2.2;Variable Expressions;215
15.2.3;Connection Managers;215
15.2.4;Project-Level Connection Managers;217
15.2.5;Control Flow;217
15.2.5.1;Conditional Execution Through Expressions and Constraints;217
15.2.5.2;Task-Level Expressions;220
15.2.6;Data Flow Expressions;220
15.2.6.1;Data Cleansing;220
15.2.6.2;Branching;221
15.2.6.3;Application of Business Rules;223
15.3;Conclusion;224
16;Chapter 11: Data Warehouse Patterns;225
16.1;Incremental Loads;225
16.1.1;What Is an Incremental Load?;225
16.1.2;Why Incremental Loads?;226
16.1.3;The Slowly Changing Dimension;226
16.1.4;Incremental Loads of Fact Data;226
16.2;Incremental Loads in SSIS;226
16.2.1;Native SSIS Components;227
16.2.1.1;The Moving Parts;227
16.2.1.2;Typical Uses;228
16.2.1.2.1;Lookup Caching Options;229
16.2.1.2.1.1;Table Cache;229
16.2.1.2.1.2;Cache Transformation and Cache Connection Manager;229
16.2.1.2.2;Load Staging;230
16.2.2;The Slowly Changing Dimension Wizard;230
16.2.3;The MERGE Statement;232
16.2.3.1;A Little Background;232
16.2.3.2;MERGE in Action;233
16.2.3.3;Auditing with MERGE;235
16.2.4;Change Data Capture (CDC);235
16.2.4.1;CDC in Integration Services;235
16.2.4.2;Change Detection in General;236
16.2.4.2.1;Checksum-Based Detection;236
16.2.4.2.2;Detection via Hashbytes;237
16.2.4.2.3;Brute Force Detection;237
16.2.4.3;Historical Load;237
16.2.4.4;Incremental Load;238
16.2.4.5;Typical Uses;240
16.3;Data Error s;240
16.3.1;Simple Errors;240
16.3.2;Missing Data;241
16.3.2.1;Use the Unknown Member;241
16.3.2.2;Add the Missing Dimension Member;242
16.3.2.3;Triage the Lookup Failures;243
16.3.3;Coding to Allow Errors;244
16.3.3.1;Fail Package on Error;245
16.3.3.2;Unhandled Errors;246
16.4;Data Warehouse ETL Workflow;246
16.4.1;Dividing Up the Work;246
16.4.2;One Package = One Unit of Work;247
16.5;Conclusion;248
17;Chapter 12: OData Source;249
17.1;Understanding the OData Protocol;249
17.1.1;Data Type Mappings;250
17.1.2;Query Options;251
17.2;Configuring the OData Connection Manager;252
17.3;Enabling Microsoft Online Services Authentication;252
17.4;Configuring the Source Component;254
17.5;Overriding Data Types;257
17.6;Conclusion;258
18;Chapter 13: Slowly Changing Dimensions;259
18.1;The Slowly Changing Dimension Transform;259
18.1.1;Running the Wizard;260
18.1.2;Using the Transformations;265
18.1.3;Optimizing Performance;266
18.1.3.1;The Slowly Changing Dimension Transform;267
18.1.3.2;OLE DB Command Transforms;267
18.1.3.3;OLE DB Destination;267
18.2;Third-Party SCD Components;267
18.3;Merge Pattern;268
18.3.1;Handling Type 1 Changes;269
18.3.2;Handling Type 2 Changes;270
18.4;Conclusion;270
19;Chapter 14: Loading the Cloud;272
19.1;Interacting with the Cloud;272
19.2;Incremental Loads to Azure SQL Database;273
19.2.1;Change Detection;273
19.2.2;New Rows (Only);273
19.3;Building the Cloud Loader;274
19.4;Conclusion;277
20;Chapter 15: Logging and Reporting Patterns;278
20.1;Package Logging and Reporting;278
20.1.1;Setting Up Package Logging;278
20.1.2;Reporting on Package Logging;279
20.1.3;Design Pattern: Package Executions;280
20.2;Catalog Logging and Reporting;280
20.2.1;Setting Up Catalog Logging;280
20.2.2;Catalog Tables;282
20.2.3;Changing Logging Levels After the Fact;283
20.3;Design Patterns;284
20.3.1;Changing the Logging Level;284
20.3.2;Using the Existing Reports;286
20.3.3;Creating New Reports;287
20.4;Summary;288
21;Chapter 16: Parent-Child Patterns;289
21.1;Master Package Pattern;289
21.1.1;Assign the Child Package;290
21.1.2;Configure Parameter Binding;291
21.2;Dynamic Child Package Pattern;292
21.3;Child-to-Parent Variable Pattern;298
21.4;Conclusion;299
22;Chapter 17: Configuration;300
22.1;Parameters;300
22.1.1;Configuring Your Package Using Parameters;302
22.1.2;Using the Parametrize Dialog;304
22.1.3;Creating Visual Studio Configurations;305
22.1.4;Specifying Entry-Point Packages;307
22.2;Connection Managers;308
22.3;Parameter Configuration on the Server;308
22.3.1;Default Configuration;309
22.3.2;Server Environments;310
22.3.3;Default Parameter Values Using T-SQL;312
22.3.4;Package Execution Through the SSIS Catalog;312
22.4;Parameters with DTEXEC;315
22.4.1;Projects on the File System;315
22.4.2;Projects in the SSIS Catalog;316
22.5;Dynamic Configurations;317
22.5.1;Configuring from a Database Table;318
22.5.1.1;Creating the Database Table;318
22.5.1.2;Retrieving Configuration Values with an Execute SQL Task;318
22.5.2;Setting Values Using a Script Task;321
22.5.3;Dynamic Package Executions;322
22.6;Conclusion;324
23;Chapter 18: Deployment;325
23.1;Project Deployment Model;325
23.2;SSIS Catalog;326
23.3;Deployment Methods;328
23.3.1;Deployment from the Command Line;329
23.3.2;Deployment Using Custom Code;330
23.3.3;Deployment Using PowerShell;331
23.3.4;Deployment Using SQL;332
23.4;Package Deployment Model;333
23.5;Conclusion;335
24;Chapter 19: Business Intelligence Markup Language;336
24.1;A Brief History of Business Intelligence Markup Language;336
24.2;Building Your First Biml File;337
24.3;Building a Basic Incremental Load SSIS Package;340
24.3.1;Creating Databases and Tables;340
24.3.2;Adding Metadata;342
24.3.3;Specifying a Data Flow Task;343
24.3.4;Adding Transforms;343
24.3.5;Testing the Biml;349
24.4;Using Biml as an SSIS Design Patterns Engine;353
24.5;Time for a Test;360
24.6;Conclusion;361
25;Chapter 20: Biml and SSIS Frameworks;362
25.1;Using Biml with an SSIS Framework;362
25.1.1;Adding SSIS Package Metadata to the Framework;362
25.1.2;Executing the Biml File;367
25.2;Generating the SSIS Command-Line;368
25.3;Summarizing;369
26;Chapter 21:Evolution of an SSIS Framework;370
26.1;Starting in the Middle;370
26.1.1;Introducing SSIS Applications;380
26.1.2;A Note About Relationships;382
26.1.3;Retrieving SSIS Applications in T-SQL;385
26.1.4;Retrieving SSIS Applications in SSIS;389
26.2;Monitoring Execution;392
26.2.1;Building Application Instance Logging;392
26.2.2;Building Package Instance Logging;399
26.2.3;Building Error Logging;403
26.3;Reporting Execution Metrics;413
26.4;Conclusion;427
27;Index;428

Über Autor(innen)

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.