Professional Microsoft SQL Server 2014 Integration Services

Brian Knight is the founder of Pragmatic Works and co-founder of SQLServerCentral.com and JumpstartTV.com.Devin Knight is a SQL Server MVP, the Training Director at Pragmatic Works, and an author on six SQL Server books.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, 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.Wrox Professional guides are planned and written by working programmers to meet the real-world needs of programmers, developers, and IT professionals. Focused and relevant, they address the issues technology professionals face every day. They provide examples, practical solutions, and expert education in new technologies, all designed to help programmers do a better job.

"This book is suitable for both developers that are new to SSIS development, and experienced developers looking for a more complete understanding. I highly recommend it." (I Programmer, July 2014)

• Introduction xxviiChapter 1: Welcome to SQL Server Integration Services 1SQL Server SSIS Historical Overview 2What’s New in SSIS 2Tools of the Trade 3Import and Export Wizard 3The SQL Server Data Tools Experience 4SSIS Architecture 5Packages 5Control Flow 5Data Flow 9Variables 14Parameters 14Error Handling and Logging 14Editions of SQL Server 14Summary 15Chapter 2: The SSIS Tools 17Import and Export Wizard 17SQL Server Data Tools 24The Solution Explorer Window 26The SSIS Toolbox 27The Properties Windows 28The SSIS Package Designer 28Control Flow 29Connection Managers 32Variables 33Data Flow 34Parameters 35Event Handlers 35Package Explorer 36Executing a Package 37Management Studio 37Summary 37Chapter 3: SSIS Tasks 39SSIS Task Objects 40Using the Task Editor 40The Task Editor Expressions Tab 41Looping and Sequence Tasks 41Script Task (.NET) 41Analysis Services Tasks 44Analysis Services Execute DDL Task 44Analysis Services Processing Task 44Data Mining Query Task 46Data Flow Task 47Data Preparation Tasks 48Data Profiler 48File System Task 50Archiving a File 52FTP Task 53Getting a File Using FTP 54Web Service Task 56Retrieving Data Using the Web Service Task and XML Source Component 59XML Task 62Validating an XML File 64RDBMS Server Tasks 66Bulk Insert Task 66Using the Bulk Insert Task 69Execute SQL Task 71Workflow Tasks 82Execute Package Task 82Execute Process Task 84Message Queue Task 86Send Mail Task 87WMI Data Reader Task 88WMI Event Watcher Task 91Polling a Directory for the Delivery of a File 91SMO Administration Tasks 92Transfer Database Task 93Transfer Error Messages Task 94Transfer Logins Task 94Transfer Master Stored Procedures Task 95Transfer Jobs Task 96Transfer SQL Server Objects Task 96Summary 97Chapter 4: The Data Flow 99Understanding the Data Flow 99Data Viewers 100Sources 101OLE DB Source 102Excel Source 104Flat File Source 105Raw File Source 110XML Source 110ADO.NET Source 111Destinations 111Excel Destination 112Flat File Destination 112OLE DB Destination 112Raw File Destination 113Recordset Destination 114Data Mining Model Training 114DataReader Destination 114Dimension and Partition Processing 114Common Transformations 115Synchronous versus Asynchronous Transformations 115Aggregate 115Conditional Split 117Data Conversion 118Derived Column 119Lookup 121Row Count 121Script Component 122Slowly Changing Dimension 123Sort 123Union All 125Other Transformations 126Audit 126Character Map 128Copy Column 128Data Mining Query 129DQS Cleansing 130Export Column 130Fuzzy Lookup 132Fuzzy Grouping 139Import Column 143Merge 145Merge Join 146Multicast 146OLE DB Command 147Percentage and Row Sampling 148Pivot Transform 149Unpivot 152Term Extraction 154Term Lookup 158Data Flow Example 160Summary 164Chapter 5: Using Variables, Parameters, and Expressions 165Dynamic Package Objects 166Variable Overview 166Parameter Overview 166Expression Overview 167Understanding Data Types 168SSIS Data Types 168Date and Time Type Support 170How Wrong Data Types and Sizes Can Affect Performance 171Unicode and Non-Unicode Conversion Issues 171Casting in SSIS Expressions 173Using Variables and Parameters 174Defining Variables 174Defining Parameters 175Variable and Parameter Data Types 176Working with Expressions 177C#-Like? Close, but Not Completely 178The Expression Builder 179Syntax Basics 180Using Expressions in SSIS Packages 194Summary 204Chapter 6: Containers 205Task Host Containers 205Sequence Containers 206Groups 207For Loop Container 207Foreach Loop Container 210Foreach File Enumerator Example 211Foreach ADO Enumerator Example 213Summary 218Chapter 7: Joining Data 219The Lookup Transformation 220Using the Merge Join Transformation 221Contrasting SSIS and the Relational Join 222Lookup Features 224Building the Basic Package 225Using a Relational Join in the Source 227Using the Merge Join Transformation 230Using the Lookup Transformation 235Full-Cache Mode 235No-Cache Mode 239Partial-Cache Mode 240Multiple Outputs 243Expressionable Properties 246Cascaded Lookup Operations 247Cache Connection Manager and Cache Transform 249Summary 252Chapter 8: Creating an End-to-End Package 253Basic Transformation Tutorial 253Creating Connections 254Creating the Control Flow 257Creating the Data Flow 257Completing the Package 259Saving the Package 260Executing the Package 260Typical Mainframe ETL with Data Scrubbing 261Creating the Data Flow 263Handling Dirty Data 263Finalizing 268Handling More Bad Data 269Looping and the Dynamic Tasks 271Looping 271Making the Package Dynamic 272Summary 274Chapter 9: Scripting in SSIS 275Introducing SSIS Scripting 276Getting Started in SSIS Scripting 277Selecting the Scripting Language 277Using the VSTA Scripting IDE 278Example: Hello World 279Adding Code and Classes 281Using Managed Assemblies 282Example: Using Custom .NET Assemblies 283Using the Script Task 286Configuring the Script Task Editor 287The Script Task Dts Object 288Accessing Variables in the Script Task 289Connecting to Data Sources in a Script Task 293Raising an Event in a Script Task 303Writing a Log Entry in a Script Task 309Using the Script Component 310Differences from a Script Task 310Configuring the Script Component Editor 311Accessing Variables in a Script Component 313Connecting to Data Sources in a Script Component 314Raising Events 314Logging 315Example: Data Validation 316Synchronous versus Asynchronous 324Essential Coding, Debugging, and Troubleshooting Techniques 327Structured Exception Handling 327Script Debugging and Troubleshooting 330Summary 333Chapter 10: Advanced Data Cleansing in SSIS 335Advanced Derived Column Use 336Text Parsing Example 338Advanced Fuzzy Lookup and Fuzzy Grouping 340Fuzzy Lookup 340Fuzzy Grouping 347DQS Cleansing 350Data Quality Services 351DQS Cleansing Transformation 355Master Data Management 358Master Data Services 359Summary 362Chapter 11: Incremental Loads in SSIS 363Control Table Pattern 363Querying the Control Table 364Querying the Source Table 366Updating the Control Table 366SQL Server Change Data Capture 367Benefits of SQL Server CDC 368Preparing CDC 369Capture Instance Tables 371The CDC API 372Using the SSIS CDC Tools 374Summary 379Chapter 12: Loading a Data Warehouse 381Data Profiling 383Initial Execution of the Data Profiling Task 383Reviewing the Results of the Data Profiling Task 386Turning Data Profile Results into Actionable ETL Steps 390Data Extraction and Cleansing 391Dimension Table Loading 391Loading a Simple Dimension Table 392Loading a Complex Dimension Table 397Considerations and Alternatives to the SCD Transformation 408Fact Table Loading 409SSAS Processing 421Using a Master ETL Package 426Summary 428Chapter 13: Using the Relational Engine 429Data Extraction 430SELECT * is Bad 430WHERE is Your Friend 432Transform during Extract 433Many ANDs Make Light Work 437SORT in the Database 437Modularize 439SQL Server Does Text Files Too 440Using Set-Based Logic 444Data Loading 446Database Snapshots 446The MERGE Operator 448Summary 452Chapter 14: Accessing Heterogeneous Data 453Excel and Access 45564-Bit Support 455Working with Excel Files 457Working with Access 462Importing from Oracle 469Oracle Client Setup 469Importing Oracle Data 470Using XML and Web Services 472Configuring the Web Service Task 472Working with XML Data as a Source 483Flat Files 486Loading Flat Files 487Extracting Data from Flat Files 489ODBC 491Other Heterogeneous Sources 494Summary 495Chapter 15: Reliability and Scalability 497Restarting Packages 498Simple Control Flow 499Containers within Containers and Checkpoints 501Variations on a Theme 503Inside the Checkpoint File 505Package Transactions 507Single Package, Single Transaction 508Single Package, Multiple Transactions 509Two Packages, One Transaction 511Single Package Using a Native Transaction in SQL Server 512Error Outputs 513Scaling Out 516Architectural Features 516Scaling Out Memory Pressures 517Scaling Out by Staging Data 517Scaling Out with Parallel Loading 522Summary 528Chapter 16: Understanding and Tuning the Data Flow Engine 529The SSIS Engine 530Understanding the SSIS Data Flow and Control Flow 530Handling Workflows with the Control Flow 533Data Processing in the Data Flow 533Memory Buffer Architecture 534Types of Transformations 534Advanced Data Flow Execution Concepts 543SSIS Data Flow Design and Tuning 549Data Flow Design Practices 550Optimizing Package Processing 555Troubleshooting Data Flow Performance Bottlenecks 558Pipeline Performance Monitoring 559Summary 562Chapter 17: SS IS Software Development Life Cycle 563Introduction to Software Development Life Cycles 565SDLCs: A Brief History 566Types of Software Development Life Cycles 566Versioning and Source Code Control 567Subversion (SVN) 568Team Foundation Server, Team System, and SSIS 573Summary 590Chapter 18: Error and Event Handling 591Using Precedence Constraints 592Precedence Constraint Basics 592Advanced Precedence Constraints and Expressions 593Event Handling 601Events 602Using Event Handlers 603Event Handler Inheritance 611Breakpoints 612Error Rows 616Logging 622Logging Providers 622Log Events 623Catalog Logging 627Summary 629Chapter 19: Programming and Extending SSIS 631The Sample Components 632Component 1: Source Adapter 632Component 2: Transform 633Component 3: Destination Adapter 634The Pipeline Component Methods 634Design-Time Functionality 635Runtime 639Connection Time 640Building the Components 642Preparation 642Building the Source Component 648Building the Transformation Component 660Building the Destination Adapter 671Using the Components 679Installing the Components 679Debugging Components 680Design Time 680Building the Complete Package 682Runtime Debugging 682Upgrading to SQL Server 2014 687Summary 687Chapter 20: Adding a User Interface to Your Component 689Three Key Steps for Designing the UI: An Overview 690Building the User Interface 690Adding the Project 691Implementing IDtsComponentUI 693Setting the UITypeName 697Building the Form 699Extending the User Interface 704Runtime Connections 704Component Properties 707Handling Errors and Warnings 708Column Properties 711Other UI Considerations 712Summary 712Chapter 21: External Management and WMI Task Implementation 715External Management of SSIS with Managed Code 716Setting Up a Test SSIS Package for Demonstration Purposes 716The Managed Object Model Code Library 717Catalog Management 718Folder Management 719Environments 720The DTS Runtime Managed Code Library 722SSIS Deployment Projects 722Parameter Objects 723Server Deployment 725Executing SSIS Packages Deployed to the SSIS Catalog 726Environment References 727Package Operations 728Application Object Maintenance Operations 729Package Operations 729Package Monitoring 732Project, Folder, and Package Listing 734A Package Management Example 735Package Log Providers 745Specifying Events to Log 747Programming to Log Providers 748SQL Server 2014 Operation Logs 749Package Configurations 751Creating a Configuration 752Programming the Configuration Object 753Configuration Object 754Windows Management Instrumentation Tasks 755WMI Reader Task Explained 755WMI Data Reader Example 756WMI Event Watcher Task 762WMI Event Watcher Task Example 763Summary 766Chapter 22: Ad ministering SSIS 767Using the SSIS Catalog 768Setting the SSIS Catalog Properties 768SSISDB 771Deployment Models 772Project Deployment Model 773Package Deployment Model 775Using T-SQL with SSIS 781Executing Packages 781Using Parameters 782Querying Tables for Parameter Values 783Using Environments 784Using Data Taps 789Creating a Central SSIS Server 790Clustering SSIS 792Package Configuration 794Command-Line Utilities 798DTExec 798DTExecUI 799DTUtil 804Security 806Securing the SSIS Catalog 806Legacy Security 809Scheduling Packages 811SQL Server Agent 811Proxy Accounts 81364-Bit Issues 814Monitoring Package Executions 815Built-in Reporting 815Custom Reporting 819Performance Counters 819Summary 820Appendix A: SS IS Crib Notes 821When to Use Control Flow Tasks 821When to Use Data Flow Transforms 822Common Expressions and Scripts 824Appendix B: SS IS Internal Views and Stored Procedures 829Views 829Stored Procedures 830Appendix C: Interviewing for an ETL Developer Position 833Questions 833Answers 834Index 839