PacktLib: Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Credits

Foreword

About the Authors

About the Reviewers

www.PacktPub.com

Preface

Getting Started with SQL Server Integration Services

Introduction

Import and Export Wizard: First experience with SSIS

Getting started with SSDT

Creating the first SSIS Package

Getting familiar with Data Flow Task

SSIS 2012 versus previous versions in Developer Experience

Control Flow Tasks

Introduction

Executing T-SQL commands: Execute SQL Task

Handling file and folder operations: File System Task

Sending and receiving files through FTP: FTP Task

Executing other packages: Execute Package Task

Running external applications: Execute Process Task

Reading data from web methods: Web Service Task

Transforming, validating, and querying XML: XML Task

Profiling table statistics: Data Profiling Task

Batch insertion of data into a database: Bulk Insert Task

Querying system information: WMI Data Reader Task

Querying system events: WMI Event Watcher Task

Transferring SQL server objects: DBMS Tasks

Data Flow Task Part 1—Extract and Load

Introduction

Working with database connections in Data Flow

Working with flat files in Data Flow

Passing data between packages—Raw Source and Destination

Importing XML data with XML Source

Loading data into memory—Recordset Destination

Extracting and loading Excel data

Change Data Capture

Data Flow Task Part 2— Transformations

Introduction

Derived Column: adding calculated columns

Audit Transformation: logging in Data Flow

Aggregate Transform: aggregating the data stream

Conditional Split: dividing the data stream based on conditions

Lookup Transform: performing the Upsert scenario

OLE DB Command: executing SQL statements on each row in the data stream

Merge and Union All transformations: combining input data rows

Merge Join Transform: performing different types of joins in data flow

Multicast: creating copies of the data stream

Working with BLOB fields: Export Column and Import Column transformations

Slowly Changing Dimensions (SCDs) in SSIS

Data Flow Task Part 3—Advanced Transformation

Introduction

Pivot and Unpivot Transformations

Text Analysis with Term Lookup and Term Extraction transformations

DQS Cleansing Transformation—Cleansing Data

Fuzzy Transformations—how SSIS understands fuzzy similarities

Variables, Expressions, and Dynamism in SSIS

Introduction

Variables and data types

Using expressions in Control Flow

Using expressions in Data Flow

The Expression Task

Dynamic connection managers

Dynamic data transfer with different data structures

Containers and Precedence Constraints

Introduction

Sequence Container: putting all tasks in an executable object

For Loop Container: looping through static enumerator till a condition is met

Foreach Loop Container: looping through result set of a database query

Foreach Loop Container: looping through files using File Enumerator

Foreach Loop Container: looping through data table

Precedence Constraints: how to control the flow of task execution

Scripting

Introduction

The Script Task: Scripting through Control Flow

The Script Component as a Transformation

The Script Component as a Source

The Script Component as a Destination

The Asynchronous Script Component

Deployment

Introduction

Project Deployment Model: Project Deployment from SSDT

Using Integration Services Deployment Wizard and command-line utility for deployment

The Package Deployment Model, Using SSDT to deploy package

Creating and running Deployment Utility

DTUTIL—the command-line utility for deployment

Protection level: Securing sensitive data

Debugging, Troubleshooting, and Migrating Packages to 2012

Introduction

Troubleshooting with Progress and Execution Results tab

Breakpoints, Debugging the Control Flow

Handling errors in Data Flow

Migrating packages to 2012

Data Tap

Event Handling and Logging

Introduction

Logging over Legacy Deployment Model

Logging over Project Deployment Model

Using event handlers and system variables for custom logging

Execution

Introduction

Execution from SSMS

Execution from a command-line utility

Execution from a scheduled SQL Server Agent job

Restartability and Robustness

Introduction

Parameters: Passing values to packages from outside

Package configuration: Legacy method to inter-relation

Transactions: Doing multiple operations atomic

Checkpoints: The power of restartability

SSIS reports and catalog views

Programming SSIS

Introduction

Creating and configuring Control Flow Tasks programmatically

Working with Data Flow components programmatically

Executing and managing packages programmatically

Creating and using Custom Tasks

Performance Boost in SSIS

Introduction

Control Flow Task and variables considerations for boosting performance

Data Flow best practices in Extract and Load

Data Flow best practices in Transformations

Working with buffer size

Working with performance counters