Professional Microsoft SQL Server 2012 Integration Services

Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpstartTV.com. Erik Veerman is a consultant, author, trainer, and a mentor with SolidQ. His award-winning industry recognition includes Microsoft's BI Solution of the Year and SQL Server Magazine's Innovator Cup. Jessica M. Moss is a well-known architect, speaker, author, and Microsoft MVP of SQL Server business intelligence who has created data warehousing solutions for a variety of industries. Mike Davis, MCTS, MCITP, is the Managing Project Lead at Pragmatic Works. He has co-authored several business intelligence books and regularly speaks at SQL events. Chris Rock is a software developer and program manager at Pragmatic Works, where he develops custom SSIS components and software.

INTRODUCTION xxix CHAPTER 1: WELCOME TO SQL SERVER INTEGRATION SERVICES 1 SQL Server SSIS Historical Overview 2 What s New in SSIS 2 Tools of the Trade 2 Import and Export Wizard 3 The SQL Server Data Tools Experience 4 SSIS Architecture 5 Packages 5 Control Flow 6 Data Flow 9 Variables 13 Parameters 14 Error Handling and Logging 14 Editions of SQL Server 14 Summary 15 CHAPTER 2: THE SSIS TOOLS 17 Import and Export Wizard 17 SQL Server Data Tools 23 Creating Your First Package 25 The Solution Explorer Window 26 The Toolbox 27 The Properties Windows 28 The SSIS Package Designer 28 Control Flow 29 Connection Managers 32 Variables 32 Data Flow 34 Parameters 34 Event Handlers 34 Package Explorer 35 Executing a Package 36 Management Studio 36 Summary 37 CHAPTER 3: SSIS TASKS 39 SSIS Task Objects 40 Using the Task Editor 40 The Task Editor Expressions Tab 40 SSDTCommon Properties 41 Looping and Sequence Tasks 42 Script Task (.NET) 43 Analysis Services Tasks 45 Analysis Services Execute DDL Task 45 Analysis Services Processing Task 46 Data Mining Query Task 46 Data Flow Task 47 Data Preparation Tasks 48 Data Profi ler 48 File System Task 50 Archiving a File 52 FTP Task 53 Getting a File Using FTP 54 Web Service Task 55 Retrieving Data Using the Web Service Task and XML Source Component 57 XML Task 60 Validating an XML File 62 RDBMS Server Tasks 64 Bulk Insert Task 64 Using the Bulk Insert Task 67 Execute SQL Task 68 Workfl ow Tasks 80 Execute Package Task 80 Execute Process Task 81 Message Queue Task 82 Send Mail Task 83 WMI Data Reader Task 84 WMI Event Watcher Task 86 Polling a Directory for the Delivery of a File 86 SMO Administration Tasks 87 Transfer Database Task 88 Transfer Error Messages Task 89 Transfer Logins Task 89 Transfer Master Stored Procedures Task 90 Transfer Jobs Task 91 Transfer SQL Server Objects Task 91 Summary 92 CHAPTER 4: CONTAINERS 93 Task Host Containers 93 Sequence Containers 94 Groups 95 For Loop Container 95 Foreach Loop Container 97 Foreach File Enumerator Example 98 Foreach ADO Enumerator Example 100 Summary 103 CHAPTER 5: THE DATA FLOW 105 Understanding the Data Flow 105 Data Viewers 106 Sources 106 OLE DB Source 107 Excel Source 109 Flat File Source 110 Raw File Source 114 XML Source 115 ADO.NET Source 115 Destinations 115 Excel Destination 116 Flat File Destination 116 OLE DB Destination 116 Raw File Destination 117 Recordset Destination 117 SQL Server and Mobile Destinations 118 Data Mining Model Training 118 DataReader Destination 118 Dimension and Partition Processing 118 Common Transformations 119 Synchronous versus Asynchronous Transformations 119 Aggregate 119 Conditional Split 121 Data Conversion 122 Derived Column 122 Lookup 123 Row Count 124 Script Component 125 Slowly Changing Dimension 126 Sort 126 Union All 127 Other Transformations 128 Audit 128 Character Map 129 Copy Column 130 Data Mining Query 130 DQS Cleansing 131 Export Column 131 Fuzzy Lookup 133 Fuzzy Grouping 138 Import Column 142 Merge 144 Merge Join 144 Multicast 145 OLE DB Command 145 Percentage and Row Sampling 147 Pivot Transform 147 Unpivot 150 Term Extraction 152 Term Lookup 156 Data Flow Example 157 Summary 160 CHAPTER 6: USING VARIABLES, PARAMETERS, AND EXPRESSIONS 161 Dynamic Package Objects 162 Variable Overview 162 Parameter Overview 162 Expression Overview 163 Understanding Data Types 164 SSIS Data Types 164 Date and Time Type Support 166 How Wrong Data Types and Sizes Can Affect Performance 167 Unicode and Non-Unicode Conversion Issues 167 Casting in SSIS Expressions 169 Using Variables and Parameters 170 Defi ning Variables 170 Defining Parameters 171 Variable and Parameter Data Types 172 Working with Expressions 173 C#-Like? Close, but Not Completely 174 The Expression Builder 175 Syntax Basics 176 Using Expressions in SSIS Packages 190 Summary 200 CHAPTER 7: JOINING DATA 201 The Lookup Transformation 202 Using the Merge Join Transformation 203 Contrasting SSIS and the Relational Join 203 Lookup Features 206 Building the Basic Package 207 Using a Relational Join in the Source 209 Using the Merge Join Transformation 211 Using the Lookup Transformation 216 Full-Cache Mode 216 No-Cache Mode 219 Partial-Cache Mode 220 Multiple Outputs 223 Expressionable Properties 226 Cascaded Lookup Operations 227 Cache Connection Manager and Cache Transform 229 Summary 231 CHAPTER 8: CREATING AN END-TO-END PACKAGE 233 Basic Transformation Tutorial 233 Creating Connections 234 Creating the Control Flow 237 Creating the Data Flow 237 Completing the Package 239 Saving the Package 239 Executing the Package 240 Typical Mainframe ETL with Data Scrubbing 241 Creating the Data Flow 242 Handling Dirty Data 242 Finalizing 246 Handling More Bad Data 247 Looping and the Dynamic Tasks 250 Looping 250 Making the Package Dynamic 250 Summary 252 CHAPTER 9: SCRIPTING IN SSIS 253 Introducing SSIS Scripting 253 Getting Started in SSIS Scripting 255 Selecting the Scripting Language 255 Using the VSTA Scripting IDE 256 Example: Hello World 257 Adding Code and Classes 259 Using Managed Assemblies 260 Example: Using Custom .NET Assemblies 261 Using the Script Task 264 Confi guring the Script Task Editor 265 The Script Task Dts Object 266 Accessing Variables in the Script Task 267 Connecting to Data Sources in a Script Task 271 Raising an Event in a Script Task 281 Writing a Log Entry in a Script Task 287 Using the Script Component 288 Diff erences from a Script Task 288 Confi guring the Script Component Editor 289 Accessing Variables in a Script Component 291 Connecting to Data Sources in a Script Component 292 Raising Events 292 Logging 293 Example: Data Validation 294 Synchronous versus Asynchronous 302 Essential Coding, Debugging, and Troubleshooting Techniques 305 Structured Exception Handling 305 Script Debugging and Troubleshooting 308 Summary 311 CHAPTER 10: LOADING A DATA WAREHOUSE 313 Data Profiling 315 Initial Execution of the Data Profiling Task 315 Reviewing the Results of the Data Profiling Task 317 Turning Data Profi le Results into Actionable ETL Steps 321 Data Extraction and Cleansing 322 Dimension Table Loading 322 Loading a Simple Dimension Table 323 Loading a Complex Dimension Table 327 Considerations and Alternatives to the SCD Transformation 335 Fact Table Loading 337 SSAS Processing 345 Using a Master ETL Package 350 Summary 351 CHAPTER 11: ADVANCED DATA CLEANSING IN SSIS 353 Advanced Derived Column Use 354 Text Parsing Example 355 Advanced Fuzzy Lookup and Fuzzy Grouping 357 Fuzzy Lookup 357 Fuzzy Grouping 363 DQS Cleansing 366 Data Quality Services 366 DQS Cleansing Transformation 370 Summary 373 CHAPTER 12: USING THE RELATIONAL ENGINE 375 Data Extraction 376 SELECT * Is Bad 376 WHERE Is Your Friend 377 Transform during Extract 378 Many ANDs Make Light Work 381 SORT in the Database 382 Modularize 384 SQL Server Does Text Files Too 385 Using Set-Based Logic 389 SQL Server Change Data Capture 391 Benefi ts of SQL Server CDC 392 Preparing CDC 393 Capture Instance Tables 394 The CDC API 396 Using the New SSIS CDC Tools 398 Querying CDC in SSIS 401 Data Loading 405 Database Snapshots 406 The MERGE Operator 408 Summary 411 CHAPTER 13: ACCESSING HETEROGENEOUS DATA 413 Excel and Access 415 64-Bit Support 415 Working with Excel Files 417 Working with Access 421 Importing from Oracle 427 Oracle Client Setup 428 Importing Oracle Data 429 Using XML and Web Services 431 Confi guring the Web Service Task 431 Working with XML Data as a Source 439 Flat Files 442 Loading Flat Files 443 Extracting Data from Flat Files 444 ODBC 447 Other Heterogeneous Sources 450 Summary 451 CHAPTER 14: RELIABILITY AND SCALABILITY 453 Restarting Packages 453 Simple Control Flow 455 Containers within Containers and Checkpoints 457 Variations on a Theme 459 Inside the Checkpoint File 461 Package Transactions 463 Single Package, Single Transaction 464 Single Package, Multiple Transactions 466 Two Packages, One Transaction 468 Single Package Using a Native Transaction in SQL Server 469 Error Outputs 471 Scaling Out 473 Architectural Features 474 Scaling Out Memory Pressures 474 Scaling Out by Staging Data 475 Scaling Out with Parallel Loading 479 Summary 485 CHAPTER 15: UNDERSTANDING AND TUNING THE DATA FLOW ENGINE 487 The SSIS Engine 488 Understanding the SSIS Data Flow and Control Flow 488 Handling Workfl ows with the Control Flow 491 Data Processing in the Data Flow 491 Memory Buff er Architecture 492 Types of Transformations 493 Advanced Data Flow Execution Concepts 501 SSIS Data Flow Design and Tuning 508 Data Flow Design Practices 508 Optimizing Package Processing 513 Troubleshooting Data Flow Performance Bottlenecks 516 Pipeline Performance Monitoring 518 Summary 520 CHAPTER 16: SSIS SOFTWARE DEVELOPMENT LIFE CYCLE 521 Introduction to Software Development Life Cycles 523 SDLCs: A Brief History 524 Types of Software Development Life Cycles 524 Versioning and Source Code Control 525 Subversion (SVN) 526 Team Foundation Server, Team System, and SSIS 533 Summary 547 CHAPTER 17: ERROR AND EVENT HANDLING 549 Using Precedence Constraints 549 Precedence Constraint Basics 550 Advanced Precedence Constraints and Expressions 551 Event Handling 557 Events 558 Using Event Handlers 560 Event Handler Inheritance 567 Breakpoints 569 Error Rows 572 Logging 576 Logging Providers 577 Log Events 577 Catalog Logging 582 Summary 584 CHAPTER 18: PROGRAMMING AND EXTENDING SSIS 585 The Sample Components 586 Component 1: Source Adapter 586 Component 2: Transform 587 Component 3: Destination Adapter 588 The Pipeline Component Methods 588 Design-Time Functionality 589 Runtime 593 Connection Time 594 Building the Components 596 Preparation 596 Building the Source Component 602 Building the Transformation Component 614 Building the Destination Adapter 625 Using the Components 633 Installing the Components 633 Debugging Components 634 Design Time 635 Building the Complete Package 636 Runtime Debugging 637 Upgrading to SQL Server 2012 641 Summary 641 CHAPTER 19: ADDING A USER INTERFACE TO YOUR COMPONENT 643 Three Key Steps for Designing the UI: An Overview 644 Building the User Interface 644 Adding the Project 645 Implementing IDtsComponentUI 647 Setting the UITypeName 651 Building the Form 653 Extending the User Interface 658 Runtime Connections 658 Component Properties 661 Handling Errors and Warnings 663 Column Properties 665 Other UI Considerations 667 Summary 667 CHAPTER 20: EXTERNAL MANAGEMENT AND WMI TASK IMPLEMENTATION 669 External Management of SSIS with Managed Code 670 Setting Up a Test SSIS Package for Demonstration Purposes 670 The Managed Object Model Code Library 671 Catalog Management 672 Folder Management 673 Environments 674 The DTS Runtime Managed Code Library 676 SSIS Deployment Projects 676 Parameter Objects 677 Server Deployment 679 Executing SSIS Packages Deployed to the SSIS Catalog 680 EnvironmentReferences 681 Package Operations 682 Application Object Maintenance Operations 683 Package Operations 683 Package Monitoring 686 Project, Folder, and Package Listing 688 A Package Management Example 689 Package Log Providers 699 Specifying Events to Log 701 Programming to Log Providers 702 SQL Server 2012 Operation Logs 703 Package Confi gurations 705 Creating a Confi guration 706 Programming the Confi guration Object 707 Confi guration Object 708 Windows Management Instrumentation Tasks 709 WMI Reader Task Explained 709 WMI Data Reader Example 710 WMI Event Watcher Task 715 WMI Event Watcher Task Example 716 Summary 718 CHAPTER 21: USING SSIS WITH EXTERNAL APPLICATIONS 719 InfoPath Documents 720 ASP.NET Applications 727 Winform .NET Applications 731 Executing a Package with T-SQL 736 Summary 741 CHAPTER 22: ADMINISTERING SSIS 743 Using the SSIS Catalog 743 Setting the SSIS Catalog Properties 744 SSISDB 747 Deployment Models 748 Project Deployment Model 749 Package Deployment Model 751 Using T-SQL with SSIS 757 Executing Packages 757 Using Parameters 758 Querying Tables for Parameter Values 759 Using Environments 760 Using Data Taps 765 Creating a Central SSIS Server 766 Clustering SSIS 768 Package Confi guration 770 Command-Line Utilities 774 DTExec 774 DTExecUI 775 DTUtil 780 Security 782 Securing the SSIS Catalog 782 Legacy Security 785 Scheduling Packages 787 SQL Server Agent 787 Proxy Accounts 789 64-Bit Issues 790 Monitoring Package Executions 791 Built-in Reporting 791 Custom Reporting 795 Performance Counters 796 Summary 796 CHAPTER 23: CASE STUDY: A PROGRAMMATIC EXAMPLE 797 What You Will Take Away 798 Background 798 Business Problem 799 Solution Summary 799 Solution Architecture 801 Naming Conventions and Tips 804 Additional SSIS Tips Before You Start a Large Project 805 Data Architecture 805 File Storage Location Setup 806 Bank ACH Payments 806 Lockbox Files 807 PayPal or Direct Credits to the Corporate Account 808 Case Study Database Model 808 Database Setup 810 Case Study Load Packages 819 Bank File Load Package 819 ACH Load Package 845 E-mail Load Package 861 Testing 866 Case Study Invoice Matching Process 867 Matching Process Control Flow 867 Matching Process High-Confidence Data Flow 870 Matching Process Medium-Confidence Data Flow 875 Interpreting the Results 879 Creating a Parent Driver Package 880 Driver Package Setup 880 Summary 881 INDEX 883