SQL Server 2012 Integration Services Design Patterns (Expert's Voice in SQL Server)

SQL Server 2012 Integration Services Design Patternsis a book of recipes for SQL Server Integration Services (SSIS). Design patternsin the book show how tosolve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution. SQL Server 2012 Integration Services Design Patternsdoes not focus on the problems to be solved; instead, the book delves 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, SQL Server 2012 Integration Services Design Patterns 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, and Dependency Services. Takes you through solutions to several common data integration challenges Demonstrates new features in SQL Server 2012 Integration Services Teaches SSIS using practical examples. SQL Server 2012 Integration Services Design Patterns is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In SSIS Design Patterns, we take you through several of these snippets in detail, providing the technical details of the resolution. SQL Server 2012 Integration Services Design Patterns does not focus on the problems to be solved; instead, the book delves 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, SQL Server 2012 Integration Services Design Patterns 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, and Dependency Services. Takes you through solutions to several common data integration challenges Demonstrates new features in SQL Server 2012 Integration Services Teaches SSIS using practical examples. SQL Server 2012 Integration Services Design Patterns......Page 427 Contents......Page 430 Foreword......Page 440 About the Authors......Page 441 About the Technical Reviewers......Page 443 Acknowledgments......Page 444 A Peek at the Final Product......Page 5 SQL Server Metadata......Page 7 Setting Up the Central Repository......Page 8 The Iterative Framework......Page 10 Metadata Collection......Page 18 Summary......Page 32 Building the Demonstration SSIS Package......Page 33 Debug Execution......Page 34 Execute Package Utility......Page 35 Integration Server Catalogs......Page 36 Integration Server Catalog Stored Procedures......Page 37 Adding a Data Tap......Page 41 Creating a Custom Execution Framework......Page 45 Scheduling an SSIS Package......Page 57 Scheduling a File System Package......Page 58 Running SQLAgent Jobs with the Custom Execution Framework......Page 59 Running the Custom Execution Framework with SQL Agent......Page 60 The Demo Application......Page 62 The frmMain Form......Page 63 Summary......Page 73 The Toolset......Page 74 The Script Editor......Page 75 Project Explorer......Page 76 Compiler......Page 77 The Script Task......Page 78 The Script Component......Page 80 Code Reuse......Page 81 Scripting Design Patterns......Page 82 Connection Managers and Scripting......Page 83 Using Connection Managers in the Script Component......Page 84 Variable Visibility......Page 85 Variable Syntax in Code......Page 86 Variable Data Types......Page 87 Summary......Page 88 Setting up a Source......Page 89 Selecting a SQL Server Connection Manager and Provider......Page 90 ADO.NET......Page 91 ODBC......Page 92 OLE DB......Page 93 Creating a SQL Server Source Component......Page 94 Writing a SQL Server Source Component Query......Page 96 ADO.NET Data Access......Page 97 OLE DB Data Access......Page 98 Source Assistant......Page 99 Summary......Page 101 Overview of Data Quality Services......Page 102 Using the Data Quality Client......Page 103 Knowledge Base Management......Page 104 Data Quality Projects......Page 105 Administration......Page 106 Using the Default Knowledge Base......Page 107 Using DQS with SSIS......Page 108 Handling the Output of the DQS Cleansing Transform......Page 114 Tracking Which Rows Have Been Cleansed......Page 117 Filtering Rows with the Lookup Transform......Page 118 Approving and Importing Cleansing Rules......Page 121 Summary......Page 123 DB2 Database Family......Page 124 Find Database Version......Page 125 Connecting to a DB2 Database......Page 126 Querying the DB2 Database......Page 129 DB2 Source Component Parameters......Page 130 DB2 Source Component Dynamic Queries......Page 131 Summary......Page 133 Flat File Sources......Page 134 Moving To SSIS!......Page 135 Strong-Typing the Data......Page 137 Introducing a Data-Staging Pattern......Page 139 Variable-Length-Rows......Page 142 Reading into a Data Flow......Page 143 Splitting Record Types......Page 144 Terminating the Streams......Page 145 Consuming a Footer Row......Page 147 Consuming a Header Row......Page 150 Producing a Footer Row......Page 152 Producing a Header Row......Page 159 The Archive File Pattern......Page 163 Summary......Page 168 Chapter 8: Parallel Data Warehouse Patterns......Page 169 PDW Architecture Overview......Page 170 ETL vs. ELT......Page 172 Installing the PDW Destination Adapter......Page 174 The Data Source......Page 175 The Data Source......Page 177 The Data Transformation......Page 179 The Data Destination......Page 181 Multi-Threading......Page 188 Summary......Page 189 Using the XML Source......Page 190 Dealing with Multiple Outputs......Page 191 Making Things Easier with XSLT......Page 197 Configuring the Script Component......Page 200 Processing XML with XmlSerializer......Page 205 Processing XML with XmlReader and LINQ to XML......Page 206 Summary......Page 208 What is Expression Language?......Page 209 Why Use Expressions?......Page 210 Limitations......Page 211 Package Expressions......Page 212 Connection Managers......Page 213 Conditional Execution Through Expressions and Constraints......Page 215 Data Cleansing......Page 218 Branching......Page 219 Application of Business Rules......Page 221 Summary......Page 223 Why Incremental Loads?......Page 224 Incremental Loads in SSIS......Page 225 The Moving Parts......Page 226 Typical Uses......Page 227 Load Staging......Page 228 The Slowly Changing Dimension Wizard......Page 229 A Little Background......Page 231 MERGE in Action......Page 232 Change Data Capture (CDC)......Page 234 Detection via Hashbytes......Page 235 Historical Load......Page 236 Incremental Load......Page 237 Missing Data......Page 239 Use the Unknown Member......Page 240 Add the Missing Dimension Member......Page 241 Coding to Allow Errors......Page 242 Fail Package on Error......Page 243 One Package = One Unit of Work......Page 244 Summary......Page 246 It tells you what has happened in the past......Page 247 Elements of Logging......Page 248 SSIS Catalog Logging......Page 249 Built-In Reports......Page 250 Package Logging......Page 252 Custom Logging......Page 254 What to Capture ?......Page 255 Summary......Page 256 Slowly Changing Dimension Transform......Page 257 Running the Wizard......Page 258 Using the Transformations......Page 262 Optimizing Performance......Page 263 Third Party SCD Components......Page 264 MERGE Pattern......Page 265 Handling Type 1 Changes......Page 266 Summary......Page 267 Interacting with the Cloud......Page 269 New Rows (Only)......Page 270 Building the Cloud Loader......Page 271 Summary......Page 274 Package Logging and Reporting......Page 275 Setting Up Package Logging......Page 276 Setting Up Catalog Logging......Page 277 Catalog Tables......Page 279 Changing Logging Levels After the Fact......Page 280 Changing the Logging Level......Page 281 Utilizing Existing Reports......Page 282 Creating New Reports......Page 283 Summary......Page 285 Master Package Pattern......Page 286 Assign the Child Package......Page 287 Configure Parameter Binding......Page 288 Dynamic Child Package Pattern......Page 289 Child to Parent Variable Pattern......Page 293 Summary......Page 295 A Brief History of Business Intelligence Markup Language......Page 296 Building Your First Biml File......Page 297 Building a Basic Incremental Load SSIS Package......Page 300 Testing the Biml......Page 309 Using Biml as an SSIS Design Patterns Engine......Page 313 Time for a Test......Page 319 Summary......Page 321 Parameters......Page 322 Configuring Your Package Using Parameters......Page 324 Creating Visual Studio Configurations......Page 327 Connection Managers......Page 329 Default Configuration......Page 330 Server Environments......Page 332 Package Execution through the SSIS Catalog......Page 334 Parameters with DTEXEC......Page 336 Projects on the File System......Page 337 Projects in the SSIS Catalog......Page 338 Creating the Database Table......Page 339 Retrieving Configuration Values with an Execute SQL Task......Page 340 Dynamic Package Executions......Page 343 Summary......Page 345 Project Deployment Model......Page 346 SSIS Catalog......Page 347 Deployment Methods......Page 349 Deployment from the Command Line......Page 350 Deployment Using Custom Code......Page 351 Deployment Using PowerShell......Page 352 Deployment Using SQL......Page 353 Package Deployment Model......Page 354 Summary......Page 355 Why estimate?......Page 356 It’s difficult because it requires—communication......Page 357 It’s difficult because it relies on technology......Page 358 Don’t forget the little things......Page 359 Plan for the unexpected......Page 360 Summary......Page 361 Starting in the Middle......Page 362 Introducing SSIS Applications......Page 371 A Note About Relationships......Page 373 Retrieving SSIS Applications in T-SQL......Page 376 Retrieving SSIS Applications in SSIS......Page 379 Monitoring Execution......Page 381 Building Application Instance Logging......Page 382 Building Package Instance Logging......Page 388 Building Error Logging......Page 392 Reporting Execution Metrics......Page 401 Summary......Page 416 Index......Page 417 __SQL Server 2012 Integration Services Design Patterns__ is a book of recipes for SQL Server Integration Services (SSIS). Design patterns in the book show how to solve common problems encountered when developing data integration solutions. Because you do not have to build the code from scratch each time, using design patterns improves your efficiency as an SSIS developer. In __SSIS Design Patterns__, we take you through several of these snippets in detail, providing the technical details of the resolution. __SQL Server 2012 Integration Services Design Patterns__ does not focus on the problems to be solved; instead, the book delves 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, __SQL Server 2012 Integration Services Design Patterns__ 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, and Dependency Services. * Takes you through solutions to several common data integration challenges * Demonstrates new features in SQL Server 2012 Integration Services * Teaches SSIS using practical examples