Professional: Microsoft® SQL Server® 2012 Integration Services -- Contents -- Introduction -- 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 -- Creating Your First Package -- The Solution Explorer Window -- The 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 -- SSDTCommon Properties -- 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: Containers -- Task Host Containers -- Sequence Containers -- Groups -- For Loop Container -- Foreach Loop Container -- Foreach File Enumerator Example -- Foreach ADO Enumerator Example -- Summary -- Chapter 5: 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 -- SQL Server and Mobile Destinations -- 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 6: 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 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: 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 11: 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 -- Summary -- Chapter 12: 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 -- SQL Server Change Data Capture -- Benefits of SQL Server CDC -- Preparing CDC -- Capture Instance Tables -- The CDC API -- Using the New SSIS CDC Tools -- Querying CDC in SSIS -- Data Loading -- Database Snapshots -- The MERGE Operator -- Summary -- Chapter 13: 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 14: 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 15: Understanding and Tuning the 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 16: 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 17: 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 18: 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.