Professional Microsoft SQL Server 2014 Integration Services

Description

Professional Microsoft® SQL Server® 2014 Integration Services Copyright About the Authors About The Technical Editor Credits Acknowledgments Contents Introduction Who This Book Is For What This Book Covers How This Book Is Structured What You Need to Use This Book Conventions Source Code Errata P2P.Wrox.Com Chapter 1: Welcome to SQL Server Integration Services SQL Server SSIS Historical Overview What’s New in SSIS Tools of the Trade Import and Export Wizard The SQL Server Data Tools Experience SSIS Architecture Packages Control Flow Data Flow Variables Parameters Error Handling and Logging Editions of SQL Server Summary Chapter 2: The SSIS Tools Import and Export Wizard SQL Server Data Tools The Solution Explorer Window The SSIS Toolbox The Properties Windows The SSIS Package Designer Control Flow Connection Managers Variables Data Flow Parameters Event Handlers Package Explorer Executing a Package Management Studio Summary Chapter 3: SSIS Tasks SSIS Task Objects Using the Task Editor The Task Editor Expressions Tab Looping and Sequence Tasks Script Task (.NET) Analysis Services Tasks Analysis Services Execute DDL Task Analysis Services Processing Task Data Mining Query Task Data Flow Task Data Preparation Tasks Data Profiler File System Task Archiving a File FTP Task Getting a File Using FTP Web Service Task Retrieving Data Using the Web Service Task and XML Source Component XML Task Validating an XML File RDBMS Server Tasks Bulk Insert Task Using the Bulk Insert Task Execute SQL Task Workflow Tasks Execute Package Task Execute Process Task Message Queue Task Send Mail Task WMI Data Reader Task WMI Event Watcher Task Polling a Directory for the Delivery of a File SMO Administration Tasks Transfer Database Task Transfer Error Messages Task Transfer Logins Task Transfer Master Stored Procedures Task Transfer Jobs Task Transfer SQL Server Objects Task Summary Chapter 4: The Data Flow Understanding the Data Flow Data Viewers Sources OLE DB Source Excel Source Flat File Source Raw File Source XML Source ADO .NET Source Destinations Excel Destination Flat File Destination OLE DB Destination Raw File Destination Recordset Destination Data Mining Model Training DataReader Destination Dimension and Partition Processing Common Transformations Synchronous versus Asynchronous Transformations Aggregate Conditional Split Data Conversion Derived Column Lookup Row Count Script Component Slowly Changing Dimension Sort Union All Other Transformations Audit Character Map Copy Column Data Mining Query DQS Cleansing Export Column Fuzzy Lookup Fuzzy Grouping Import Column Merge Merge Join Multicast OLE DB Command Percentage and Row Sampling Pivot Transform Unpivot Term Extraction Term Lookup Data Flow Example Summary Chapter 5: Using Variables, Parameters, and Expressions Dynamic Package Objects Variable Overview Parameter Overview Expression Overview Understanding Data Types SSIS Data Types Date and Time Type Support How Wrong Data Types and Sizes Can Affect Performance Unicode and Non-Unicode Conversion Issues Casting in SSIS Expressions Using Variables and Parameters Defining Variables Defining Parameters Variable and Parameter Data Types Working with Expressions C#-Like? Close, but Not Completely The Expression Builder Syntax Basics Using Expressions in SSIS Packages Summary Chapter 6: Containers Task Host Containers Sequence Containers Groups For Loop Container Foreach Loop Container Foreach File Enumerator Example Foreach ADO Enumerator Example Summary Chapter 7: Joining Data The Lookup Transformation Using the Merge Join Transformation Contrasting SSIS and the Relational Join Lookup Features Building the Basic Package Using a Relational Join in the Source Using the Merge Join Transformation Using the Lookup Transformation Full-Cache Mode No-Cache Mode Partial-Cache Mode Multiple Outputs Expressionable Properties Cascaded Lookup Operations Cache Connection Manager and Cache Transform Summary Chapter 8: Creating an End-to-End Package Basic Transformation Tutorial Creating Connections Creating the Control Flow Creating the Data Flow Completing the Package Saving the Package Executing the Package Typical Mainframe ETL with Data Scrubbing Creating the Data Flow Handling Dirty Data Finalizing Handling More Bad Data Looping and the Dynamic Tasks Looping Making the Package Dynamic Summary Chapter 9: Scripting in SSIS Introducing SSIS Scripting Getting Started in SSIS Scripting Selecting the Scripting Language Using the VSTA Scripting IDE Example: Hello World Adding Code and Classes Using Managed Assemblies Example: Using Custom .NET Assemblies Using the Script Task Configuring the Script Task Editor The Script Task Dts Object Accessing Variables in the Script Task Connecting to Data Sources in a Script Task Raising an Event in a Script Task Writing a Log Entry in a Script Task Using the Script Component Differences from a Script Task Configuring the Script Component Editor Accessing Variables in a Script Component Connecting to Data Sources in a Script Component Raising Events Logging Example: Data Validation Synchronous versus Asynchronous Essential Coding, Debugging, and Troubleshooting Techniques Structured Exception Handling Script Debugging and Troubleshooting Summary Chapter 10: Advanced Data Cleansing in SSIS Advanced Derived Column Use Text Parsing Example Advanced Fuzzy Lookup and Fuzzy Grouping Fuzzy Lookup Fuzzy Grouping DQS Cleansing Data Quality Services DQS Cleansing Transformation Master Data Management Master Data Services Summary Chapter 11: Incremental Loads in SSIS Control Table Pattern Querying the Control Table Querying the Source Table Updating the Control Table SQL Server Change Data Capture Benefits of SQL Server CDC Preparing CDC Capture Instance Tables The CDC API Using the SSIS CDC Tools Summary Chapter 12: Loading a Data Warehouse Data Profiling Initial Execution of the Data Profiling Task Reviewing the Results of the Data Profiling Task Turning Data Profile Results into Actionable ETL Steps Data Extraction and Cleansing Dimension Table Loading Loading a Simple Dimension Table Loading a Complex Dimension Table Considerations and Alternatives to the SCD Transformation Fact Table Loading SSAS Processing Using a Master ETL Package Summary Chapter 13: Using the Relational Engine Data Extraction SELECT * Is Bad WHERE Is Your Friend Transform during Extract Many ANDs Make Light Work SORT in the Database Modularize SQL Server Does Text Files Too Using Set-Based Logic Data Loading Database Snapshots The MERGE Operator Summary Chapter 14: Accessing Heterogeneous Data Excel and Access 64-Bit Support Working with Excel Files Working with Access Importing from Oracle Oracle Client Setup Importing Oracle Data Using XML and Web Services Configuring the Web Service Task Working with XML Data as a Source Flat Files Loading Flat Files Extracting Data from Flat Files ODBC Other Heterogeneous Sources Summary Chapter 15: Reliability and Scalability Restarting Packages Simple Control Flow Containers within Containers and Checkpoints Variations on a Theme Inside the Checkpoint File Package Transactions Single Package, Single Transaction Single Package, Multiple Transactions Two Packages, One Transaction Single Package Using a Native Transaction in SQL Server Error Outputs Scaling Out Architectural Features Scaling Out Memory Pressures Scaling Out by Staging Data Scaling Out with Parallel Loading Summary Chapter 16: Understanding and Tuningthe Data Flow Engine The SSIS Engine Understanding the SSIS Data Flow and Control Flow Handling Workflows with the Control Flow Data Processing in the Data Flow Memory Buffer Architecture Types of Transformations Advanced Data Flow Execution Concepts SSIS Data Flow Design and Tuning Data Flow Design Practices Optimizing Package Processing Troubleshooting Data Flow Performance Bottlenecks Pipeline Performance Monitoring Summary Chapter 17: SSIS Software Development Life Cycle Introduction to Software Development Life Cycles SDLCs: A Brief History Types of Software Development Life Cycles Versioning and Source Code Control Subversion (SVN ) Team Foundation Server, Team System, and SSIS Summary Chapter 18: Error and Event Handling Using Precedence Constraints Precedence Constraint Basics Advanced Precedence Constraints and Expressions Event Handling Events Using Event Handlers Event Handler Inheritance Breakpoints Error Rows Logging Logging Providers Log Events Catalog Logging Summary Chapter 19: Programming and Extending SSIS The Sample Components Component 1: Source Adapter Component 2: Transform Component 3: Destination Adapter The Pipeline Component Methods Design-Time Functionality Runtime Connection Time Building the Components Preparation Building the Source Component Building the Transformation Component Building the Destination Adapter Using the Components Installing the Components Debugging Components Design Time Building the Complete Package Runtime Debugging Upgrading to SQL Server 2014 Summary Chapter 20: Adding a User Interface toYour Component Three Key Steps for Designing the UI: An Overview Building the User Interface Adding the Project Implementing IDtsComponentUI Setting the UITypeName Building the Form Extending the User Interface Runtime Connections Component Properties Handling Errors and Warnings Column Properties Other UI Considerations Summary Chapter 21: External Management and WMI TaskImplementation External Management of SSIS with Managed Code Setting Up a Test SSIS Package for Demonstration Purposes The Managed Object Model Code Library Catalog Management Folder Management Environments The DTS Runtime Managed Code Library SSIS Deployment Projects Parameter Objects Server Deployment Executing SSIS Packages Deployed to the SSIS Catalog Environment References Package Operations Application Object Maintenance Operations Package Operations Package Monitoring Project, Folder, and Package Listing A Package Management Example Package Log Providers Specifying Events to Log Programming to Log Providers SQL Server 2014 Operation Logs Package Configurations Creating a Configuration Programming the Configuration Object Configuration Object Windows Management Instrumentation Tasks WMI Reader Task Explained WMI Data Reader Example WMI Event Watcher Task WMI Event Watcher Task Example Summary Chapter 22: Administering SSIS Using the SSIS Catalog Setting the SSIS Catalog Properties SSISDB Deployment Models Project Deployment Model Package Deployment Model Using T-SQL with SSIS Executing Packages Using Parameters Querying Tables for Parameter Values Using Environments Using Data Taps Creating a Central SSIS Server Clustering SSIS Package Configuration Command-Line Utilities DTExec DTExecUI DTUtil Security Securing the SSIS Catalog Legacy Security Scheduling Packages SQL Server Agent Proxy Accounts 64-Bit Issues Monitoring Package Executions Built-in Reporting Custom Reporting Performance Counters Summary Appendix A: SSIS Crib Notes When to Use Control Flow Tasks When to Use Data Flow Transforms Common Expressions and Scripts Appendix B: SSIS Internal Views and StoredProcedures Views Stored Procedures Appendix C: Interviewing for an ETL DeveloperPosition Questions Answers Index Advertisement