Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer)

**Fill the gap between planning and doing with SSIS 2014**The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and __Professional Microsoft SQL Server 2014 Integration Services__ will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage. * Implement new best practices for effective use of SSIS * Work through tutorials for hands-on learning of complex techniques * Read case studies that illustrate the more advanced concepts * Learn directly from the foremost authority on SSIS SQL Server Integration Services is a complex tool, but it's the lifeblood of your work. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. You need to make sure the right architecture is in place. __Professional Microsoft SQL Server 2014 Integration Services__ is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success. Professional Microsoft® SQL Server® 2014 Integration Services 5 Copyright 6 About the Authors 9 About The Technical Editor 10 Credits 11 Acknowledgments 13 Contents 15 Introduction 29 Who This Book Is For 29 What This Book Covers 30 How This Book Is Structured 30 What You Need to Use This Book 32 Conventions 32 Source Code 33 Errata 33 P2P.Wrox.Com 34 Chapter 1: Welcome to SQL Server Integration Services 35 SQL Server SSIS Historical Overview 36 What’s New in SSIS 36 Tools of the Trade 37 Import and Export Wizard 37 The SQL Server Data Tools Experience 38 SSIS Architecture 39 Packages 39 Control Flow 39 Data Flow 43 Variables 48 Parameters 48 Error Handling and Logging 48 Editions of SQL Server 48 Summary 49 Chapter 2: The SSIS Tools 51 Import and Export Wizard 51 SQL Server Data Tools 58 The Solution Explorer Window 60 The SSIS Toolbox 61 The Properties Windows 62 The SSIS Package Designer 62 Control Flow 63 Connection Managers 66 Variables 67 Data Flow 68 Parameters 69 Event Handlers 69 Package Explorer 70 Executing a Package 71 Management Studio 71 Summary 71 Chapter 3: SSIS Tasks 73 SSIS Task Objects 74 Using the Task Editor 74 The Task Editor Expressions Tab 75 Looping and Sequence Tasks 75 Script Task (.NET) 75 Analysis Services Tasks 78 Analysis Services Execute DDL Task 78 Analysis Services Processing Task 78 Data Mining Query Task 80 Data Flow Task 81 Data Preparation Tasks 82 Data Profiler 82 File System Task 84 Archiving a File 86 FTP Task 87 Getting a File Using FTP 88 Web Service Task 90 Retrieving Data Using the Web Service Task and XML Source Component 93 XML Task 96 Validating an XML File 98 RDBMS Server Tasks 100 Bulk Insert Task 100 Using the Bulk Insert Task 103 Execute SQL Task 105 Workflow Tasks 116 Execute Package Task 116 Execute Process Task 118 Message Queue Task 120 Send Mail Task 121 WMI Data Reader Task 122 WMI Event Watcher Task 125 Polling a Directory for the Delivery of a File 125 SMO Administration Tasks 126 Transfer Database Task 127 Transfer Error Messages Task 128 Transfer Logins Task 128 Transfer Master Stored Procedures Task 129 Transfer Jobs Task 130 Transfer SQL Server Objects Task 130 Summary 131 Chapter 4: The Data Flow 133 Understanding the Data Flow 133 Data Viewers 134 Sources 135 OLE DB Source 136 Excel Source 138 Flat File Source 139 Raw File Source 144 XML Source 144 ADO .NET Source 145 Destinations 145 Excel Destination 146 Flat File Destination 146 OLE DB Destination 146 Raw File Destination 147 Recordset Destination 148 Data Mining Model Training 148 DataReader Destination 148 Dimension and Partition Processing 148 Common Transformations 149 Synchronous versus Asynchronous Transformations 149 Aggregate 149 Conditional Split 151 Data Conversion 152 Derived Column 153 Lookup 155 Row Count 155 Script Component 156 Slowly Changing Dimension 157 Sort 157 Union All 159 Other Transformations 160 Audit 160 Character Map 162 Copy Column 162 Data Mining Query 163 DQS Cleansing 164 Export Column 164 Fuzzy Lookup 166 Fuzzy Grouping 173 Import Column 177 Merge 179 Merge Join 180 Multicast 180 OLE DB Command 181 Percentage and Row Sampling 182 Pivot Transform 183 Unpivot 186 Term Extraction 188 Term Lookup 192 Data Flow Example 194 Summary 198 Chapter 5: Using Variables, Parameters, and Expressions 199 Dynamic Package Objects 200 Variable Overview 200 Parameter Overview 200 Expression Overview 201 Understanding Data Types 202 SSIS Data Types 202 Date and Time Type Support 204 How Wrong Data Types and Sizes Can Affect Performance 205 Unicode and Non-Unicode Conversion Issues 205 Casting in SSIS Expressions 207 Using Variables and Parameters 208 Defining Variables 208 Defining Parameters 209 Variable and Parameter Data Types 210 Working with Expressions 211 C#-Like? Close, but Not Completely 212 The Expression Builder 213 Syntax Basics 214 Using Expressions in SSIS Packages 228 Summary 238 Chapter 6: Containers 239 Task Host Containers 239 Sequence Containers 240 Groups 241 For Loop Container 241 Foreach Loop Container 244 Foreach File Enumerator Example 245 Foreach ADO Enumerator Example 247 Summary 252 Chapter 7: Joining Data 253 The Lookup Transformation 254 Using the Merge Join Transformation 255 Contrasting SSIS and the Relational Join 256 Lookup Features 258 Building the Basic Package 259 Using a Relational Join in the Source 261 Using the Merge Join Transformation 264 Using the Lookup Transformation 269 Full-Cache Mode 269 No-Cache Mode 273 Partial-Cache Mode 274 Multiple Outputs 277 Expressionable Properties 280 Cascaded Lookup Operations 281 Cache Connection Manager and Cache Transform 283 Summary 286 Chapter 8: Creating an End-to-End Package 287 Basic Transformation Tutorial 287 Creating Connections 288 Creating the Control Flow 291 Creating the Data Flow 291 Completing the Package 293 Saving the Package 294 Executing the Package 294 Typical Mainframe ETL with Data Scrubbing 295 Creating the Data Flow 297 Handling Dirty Data 297 Finalizing 302 Handling More Bad Data 303 Looping and the Dynamic Tasks 305 Looping 305 Making the Package Dynamic 306 Summary 308 Chapter 9: Scripting in SSIS 309 Introducing SSIS Scripting 310 Getting Started in SSIS Scripting 311 Selecting the Scripting Language 311 Using the VSTA Scripting IDE 312 Example: Hello World 313 Adding Code and Classes 315 Using Managed Assemblies 316 Example: Using Custom .NET Assemblies 317 Using the Script Task 320 Configuring the Script Task Editor 321 The Script Task Dts Object 322 Accessing Variables in the Script Task 323 Connecting to Data Sources in a Script Task 327 Raising an Event in a Script Task 337 Writing a Log Entry in a Script Task 343 Using the Script Component 344 Differences from a Script Task 344 Configuring the Script Component Editor 345 Accessing Variables in a Script Component 347 Connecting to Data Sources in a Script Component 348 Raising Events 348 Logging 349 Example: Data Validation 350 Synchronous versus Asynchronous 358 Essential Coding, Debugging, and Troubleshooting Techniques 361 Structured Exception Handling 361 Script Debugging and Troubleshooting 364 Summary 367 Chapter 10: Advanced Data Cleansing in SSIS 369 Advanced Derived Column Use 370 Text Parsing Example 372 Advanced Fuzzy Lookup and Fuzzy Grouping 374 Fuzzy Lookup 374 Fuzzy Grouping 381 DQS Cleansing 384 Data Quality Services 385 DQS Cleansing Transformation 389 Master Data Management 392 Master Data Services 393 Summary 396 Chapter 11: Incremental Loads in SSIS 397 Control Table Pattern 397 Querying the Control Table 398 Querying the Source Table 400 Updating the Control Table 400 SQL Server Change Data Capture 401 Benefits of SQL Server CDC 402 Preparing CDC 403 Capture Instance Tables 405 The CDC API 406 Using the SSIS CDC Tools 408 Summary 413 Chapter 12: Loading a Data Warehouse 415 Data Profiling 417 Initial Execution of the Data Profiling Task 417 Reviewing the Results of the Data Profiling Task 420 Turning Data Profile Results into Actionable ETL Steps 424 Data Extraction and Cleansing 425 Dimension Table Loading 425 Loading a Simple Dimension Table 426 Loading a Complex Dimension Table 431 Considerations and Alternatives to the SCD Transformation 442 Fact Table Loading 443 SSAS Processing 455 Using a Master ETL Package 460 Summary 462 Chapter 13: Using the Relational Engine 463 Data Extraction 464 SELECT * Is Bad 464 WHERE Is Your Friend 466 Transform during Extract 467 Many ANDs Make Light Work 471 SORT in the Database 471 Modularize 473 SQL Server Does Text Files Too 474 Using Set-Based Logic 478 Data Loading 480 Database Snapshots 480 The MERGE Operator 482 Summary 486 Chapter 14: Accessing Heterogeneous Data 487 Excel and Access 489 64-Bit Support 489 Working with Excel Files 491 Working with Access 496 Importing from Oracle 503 Oracle Client Setup 503 Importing Oracle Data 504 Using XML and Web Services 506 Configuring the Web Service Task 506 Working with XML Data as a Source 517 Flat Files 520 Loading Flat Files 521 Extracting Data from Flat Files 523 ODBC 525 Other Heterogeneous Sources 528 Summary 529 Chapter 15: Reliability and Scalability 531 Restarting Packages 532 Simple Control Flow 533 Containers within Containers and Checkpoints 535 Variations on a Theme 537 Inside the Checkpoint File 539 Package Transactions 541 Single Package, Single Transaction 542 Single Package, Multiple Transactions 543 Two Packages, One Transaction 545 Single Package Using a Native Transaction in SQL Server 546 Error Outputs 547 Scaling Out 550 Architectural Features 550 Scaling Out Memory Pressures 551 Scaling Out by Staging Data 551 Scaling Out with Parallel Loading 556 Summary 562 Chapter 16: Understanding and Tuning the Data Flow Engine 563 The SSIS Engine 564 Understanding the SSIS Data Flow and Control Flow 564 Handling Workflows with the Control Flow 567 Data Processing in the Data Flow 567 Memory Buffer Architecture 568 Types of Transformations 568 Advanced Data Flow Execution Concepts 577 SSIS Data Flow Design and Tuning 583 Data Flow Design Practices 584 Optimizing Package Processing 589 Troubleshooting Data Flow Performance Bottlenecks 592 Pipeline Performance Monitoring 593 Summary 596 Chapter 17: SSIS Software Development Life Cycle 597 Introduction to Software Development Life Cycles 599 SDLCs: A Brief History 600 Types of Software Development Life Cycles 600 Versioning and Source Code Control 601 Subversion (SVN ) 602 Team Foundation Server, Team System, and SSIS 607 Summary 624 Chapter 18: Error and Event Handling 625 Using Precedence Constraints 626 Precedence Constraint Basics 626 Advanced Precedence Constraints and Expressions 627 Event Handling 635 Events 636 Using Event Handlers 637 Event Handler Inheritance 645 Breakpoints 646 Error Rows 650 Logging 656 Logging Providers 656 Log Events 657 Catalog Logging 661 Summary 663 Chapter 19: Programming and Extending SSIS 665 The Sample Components 666 Component 1: Source Adapter 666 Component 2: Transform 667 Component 3: Destination Adapter 668 The Pipeline Component Methods 668 Design-Time Functionality 669 Runtime 673 Connection Time 674 Building the Components 676 Preparation 676 Building the Source Component 682 Building the Transformation Component 694 Building the Destination Adapter 705 Using the Components 713 Installing the Components 713 Debugging Components 714 Design Time 714 Building the Complete Package 716 Runtime Debugging 716 Upgrading to SQL Server 2014 721 Summary 721 Chapter 20: Adding a User Interface to Your Component 723 Three Key Steps for Designing the UI: An Overview 724 Building the User Interface 724 Adding the Project 725 Implementing IDtsComponentUI 727 Setting the UITypeName 731 Building the Form 733 Extending the User Interface 738 Runtime Connections 738 Component Properties 741 Handling Errors and Warnings 742 Column Properties 745 Other UI Considerations 746 Summary 746 Chapter 21: External Management and WMI Task Implementation 749 External Management of SSIS with Managed Code 750 Setting Up a Test SSIS Package for Demonstration Purposes 750 The Managed Object Model Code Library 751 Catalog Management 752 Folder Management 753 Environments 754 The DTS Runtime Managed Code Library 756 SSIS Deployment Projects 756 Parameter Objects 757 Server Deployment 759 Executing SSIS Packages Deployed to the SSIS Catalog 760 Environment References 761 Package Operations 762 Application Object Maintenance Operations 763 Package Operations 763 Package Monitoring 766 Project, Folder, and Package Listing 768 A Package Management Example 769 Package Log Providers 779 Specifying Events to Log 781 Programming to Log Providers 782 SQL Server 2014 Operation Logs 783 Package Configurations 785 Creating a Configuration 786 Programming the Configuration Object 787 Configuration Object 788 Windows Management Instrumentation Tasks 789 WMI Reader Task Explained 789 WMI Data Reader Example 790 WMI Event Watcher Task 796 WMI Event Watcher Task Example 797 Summary 800 Chapter 22: Administering SSIS 801 Using the SSIS Catalog 802 Setting the SSIS Catalog Properties 802 SSISDB 805 Deployment Models 806 Project Deployment Model 807 Package Deployment Model 809 Using T-SQL with SSIS 815 Executing Packages 815 Using Parameters 816 Querying Tables for Parameter Values 817 Using Environments 818 Using Data Taps 823 Creating a Central SSIS Server 824 Clustering SSIS 826 Package Configuration 828 Command-Line Utilities 832 DTExec 832 DTExecUI 833 DTUtil 838 Security 840 Securing the SSIS Catalog 840 Legacy Security 843 Scheduling Packages 845 SQL Server Agent 845 Proxy Accounts 847 64-Bit Issues 848 Monitoring Package Executions 849 Built-in Reporting 849 Custom Reporting 853 Performance Counters 853 Summary 854 Appendix A: SSIS Crib Notes 855 When to Use Control Flow Tasks 855 When to Use Data Flow Transforms 856 Common Expressions and Scripts 858 Appendix B: SSIS Internal Views and Stored Procedures 863 Views 863 Stored Procedures 864 Appendix C: Interviewing for an ETL Developer Position 867 Questions 867 Answers 868 Index 873 Advertisement 914

Fill the gap between planning and doing with SSIS 2014

The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage.

• Implement new best practices for effective use of SSIS
• Work through tutorials for hands-on learning of complex techniques
• Read case studies that illustrate the more advanced concepts
• Learn directly from the foremost authority on SSIS

SQL Server Integration Services is a complex tool, but it's the lifeblood of your work. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. You need to make sure the right architecture is in place. Professional Microsoft SQL Server 2014 Integration Services is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success.

Fill the gap between planning and doing with SSIS 2014 The 2014 release of Microsoft's SQL Server Integration Services provides enhancements for managing extraction, transformation, and load operations, plus expanded in-memory capabilities, improved disaster recovery, increased scalability, and much more. The increased functionality will streamline your ETL processes and smooth out your workflow, but the catch is that your workflow must change. New tools come with new best practices, and Professional Microsoft SQL Server 2014 Integration Services will keep you ahead of the curve. SQL Server MVP Brian Knight is the most respected name in the business, and your ultimate guide to navigating the changes to use Microsoft SQL Server Integration Services 2014 to your utmost advantage. Implement new best practices for effective use of SSIS Work through tutorials for hands-on learning of complex techniques Read case studies that illustrate the more advanced concepts Learn directly from the foremost authority on SSIS SQL Server Integration Services is a complex tool, but it's the lifeblood of your work. You need to know it inside out, and you must understand the full potential of its capabilities in order to use it effectively. You need to make sure the right architecture is in place. Professional Microsoft SQL Server 2014 Integration Services is your roadmap to understanding SSIS on a fundamental level, and setting yourself up for success. There are two keys to a successful ETL solution: using the right enterprise ETL tool and employing the right ETL architecture in order to meet the system requirements. With SQL Server 2014 Integration Services, half the battle is won. The out-of-the-box features provide the depth and breadth of functionality and flexibility needed to create scalable solutions. Even more, the usability and rapid learning curve reduce solution development time. The second ingredient is correct ETL architecture and design. When considerable effort is spent testing scenarios and proving out designs, valuable testing, implementation, and deployment time is lost. This book fills that gap. Professional SQL Server 2014 Integration Services will, of course, fully cover the new features of the 2014 product release, expected in early 2014. The book will be about 20% revised, reflecting changes in how the product looks and works. In addition to technical updates and additions, the authors will add a new set of best practices for effective use of SSIS; more case studies, and tutorial examples to illustrate advanced concepts and techniques.