CONTENTS

Chapter 1: Welcome to SQL Server Integration Services

SQL Server SSIS Historical Overview

What’s New in SSIS

Tools of the Trade

SSIS Architecture

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 SSIS Package Designer

Management Studio

Summary

Chapter 3: SSIS Tasks

SSIS Task Objects

Looping and Sequence Tasks

Script Task (.NET)

Analysis Services Tasks

Data Flow Task

Data Preparation Tasks

RDBMS Server Tasks

Workflow Tasks

SMO Administration Tasks

Summary

Chapter 4: Containers

Task Host Containers

Sequence Containers

Groups

For Loop Container

Foreach Loop Container

Summary

Chapter 5: The Data Flow

Understanding the Data Flow

Data Viewers

Sources

Destinations

Common Transformations

Other Transformations

Data Flow Example

Summary

Chapter 6: Using Variables, Parameters, and Expressions

Dynamic Package Objects

Understanding Data Types

Using Variables and Parameters

Working with Expressions

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 the Lookup Transformation

Cache Connection Manager and Cache Transform

Summary

Chapter 8: Creating an End-to-End Package

Basic Transformation Tutorial

Typical Mainframe ETL with Data Scrubbing

Summary

Chapter 9: Scripting in SSIS

Introducing SSIS Scripting

Getting Started in SSIS Scripting

Using the Script Task

Using the Script Component

Essential Coding, Debugging, and Troubleshooting Techniques

Summary

Chapter 10: Loading A Data Warehouse

Data Profiling

Data Extraction and Cleansing

Dimension Table Loading

Fact Table Loading

SSAS Processing

Using a Master ETL Package

Summary

Chapter 11: Advanced Data Cleansing in SSIS

Advanced Derived Column Use

Advanced Fuzzy Lookup and Fuzzy Grouping

DQS Cleansing

Summary

Chapter 12: Using The Relational Engine

Data Extraction

SQL Server Change Data Capture

Data Loading

Summary

Chapter 13: Accessing Heterogeneous Data

Excel and Access

Importing from Oracle

Using XML and Web Services

Flat Files

ODBC

Other Heterogeneous Sources

Summary

Chapter 14: Reliability and Scalability

Restarting Packages

Package Transactions

Error Outputs

Scaling Out

Summary

Chapter 15: Understanding and Tuning the Data Flow Engine

The SSIS Engine

SSIS Data Flow Design and Tuning

Pipeline Performance Monitoring

Summary

Chapter 16: SSIS Software Development Life Cycle

Introduction to Software Development Life Cycles

Versioning and Source Code Control

Summary

Chapter 17: Error and Event Handling

Using Precedence Constraints

Event Handling

Breakpoints

Error Rows

Logging

Summary

Chapter 18: Programming and Extending SSIS

The Sample Components

The Pipeline Component Methods

Building the Components

Using the Components

Upgrading to SQL Server 2012

Summary

Chapter 19: Adding a User Interface to Your Component

Three Key Steps for Designing the UI: An Overview

Building the User Interface

Extending the User Interface

Other UI Considerations

Summary

Chapter 20: External Management and WMI Task Implementation

External Management of SSIS with Managed Code

Package Operations

Application Object Maintenance Operations

Package Log Providers

Package Configurations

Windows Management Instrumentation Tasks

Summary

Chapter 21: Using SSIS with External Applications

InfoPath Documents

ASP.NET Applications

Winform .NET Applications

Summary

Chapter 22: Administering SSIS

Using the SSIS Catalog

Deployment Models

Using T-SQL with SSIS

Creating a Central SSIS Server

Clustering SSIS

Package Configuration

Command-Line Utilities

Security

Scheduling Packages

64-Bit Issues

Monitoring Package Executions

Performance Counters

Summary

Chapter 23: Case Study: A Programmatic Example

What You Will Take Away

Background

Business Problem

Solution Summary

Solution Architecture

Data Architecture

Case Study Load Packages

Case Study Invoice Matching Process

Creating a Parent Driver Package

Summary

Introduction

Advertisements