Microsoft SQL Server 2012 Integration Services: An Expert Cookbook电子书

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Table of Contents

Microsoft SQL Server 2012 Integration Services: An Expert Cookbook

Credits

Foreword

About the Authors

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Getting Started with SQL Server Integration Services

Introduction

Import and Export Wizard: First experience with SSIS

Getting ready

How to do it...

How it works...

There's more...

Mapping columns

Configure transfer settings for multiple tables

Mapping data types

Querying the source database

See also

Getting started with SSDT

How to do it...

How it works...

Creating the first SSIS Package

Getting ready

How to do it...

How it works...

Getting familiar with Data Flow Task

Getting ready

How to do it...

How it works...

SSIS 2012 versus previous versions in Developer Experience

Getting ready

How to do it...

How it works...

2. Control Flow Tasks

Introduction

Executing T-SQL commands: Execute SQL Task

How to do it...

How it works...

There's more...

Full result set

XML result set

BypassPrepare

See also

Handling file and folder operations: File System Task

Getting ready

How to do it...

There's more...

Sending and receiving files through FTP: FTP Task

How to do it...

How it works...

There's more...

FTP Connection Manager's advanced options

File Connection Manager

Working with variables

Executing other packages: Execute Package Task

Getting ready

How to do it...

How it works...

There's more...

Advanced options for executing external packages

Running external applications: Execute Process Task

Getting ready

How to do it...

How it works...

Reading data from web methods: Web Service Task

How to do it...

How it works…

Transforming, validating, and querying XML: XML Task

Getting ready

How to do it...

How it works...

There's more...

XPath

Merge

Diff

Patch

See also

Profiling table statistics: Data Profiling Task

Getting ready

How to do it...

How it works...

There's more...

Batch insertion of data into a database: Bulk Insert Task

Getting ready

How to do it...

How it works...

Querying system information: WMI Data Reader Task

Getting ready

How to do it...

How it works...

There's more...

WMI code creator—a useful tool for WMI

See also

Querying system events: WMI Event Watcher Task

Getting ready

How to do it...

How it works...

Transferring SQL server objects: DBMS Tasks

Getting ready

How to do it...

How it works…

3. Data Flow Task Part 1—Extract and Load

Introduction

Working with database connections in Data Flow

Getting ready

How to do it...

How it works...

ADO.NET Source

OLE DB Destination

There's more...

Connect to ODBC data providers

Exploring All properties of connection managers

Advanced Editor

Source and Destination Assistant

Shared Connection Manager

SQL Server Destination

Fast Load option

Querying source data dynamically

See also

Working with flat files in Data Flow

Getting ready

How to do it...

How it works...

Passing data between packages—Raw Source and Destination

How to do it...

How it works...

Raw File Destination

Raw File Source

There's more...

Raise performance with Raw Files

Importing XML data with XML Source

Getting ready

How to do it...

How it works...

There's more...

Hierarchical XML data

Loading data into memory—Recordset Destination

Getting ready

How to do it...

How it works...

Extracting and loading Excel data

Getting ready

How to do it...

How it works...

Data access

Data types

Change Data Capture

Getting ready

How to do it...

How it works...

4. Data Flow Task Part 2—Transformations

Introduction

Derived Column: adding calculated columns

Getting ready

How to do it...

How it works...

Audit Transformation: logging in Data Flow

Getting ready

How to do it...

How it works...

Aggregate Transform: aggregating the data stream

How to do it...

How it works...

Conditional Split: dividing the data stream based on conditions

Getting ready

How to do it...

How it works...

Lookup Transform: performing the Upsert scenario

Getting ready

How to do it...

How it works...

Specify how to handle rows with no matching entries

Connection tab

Columns tab

Two different outputs

There's more...

Cache mode

Connection type

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

Getting ready

How to do it...

How it works...

OLE DB

Parameter markers

There's more...

Fetching the output parameter into the data stream column

Merge and Union All transformations: combining input data rows

Getting ready

How to do it...

How it works...

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

How to do it...

How it works...

Joining key(s)

Join types

There's more...

Performance issue with Sort Transformation

Order by clause

IsSorted

SortKeyPosition

Merge Join versus Lookup

Sort Transform properties

Remove rows with duplicate sort values

Sort Order

Sort Type

Comparison Flags

Pass Through

Multicast: creating copies of the data stream

Getting ready

How to do it...

How it works...

Working with BLOB fields: Export Column and Import Column transformations

Getting ready

How to do it...

How it works...

Export Column

Import Column

Slowly Changing Dimensions (SCDs) in SSIS

Getting ready

How to do it...

How it works...

5. Data Flow Task Part 3—Advanced Transformation

Introduction

Pivot and Unpivot Transformations

Getting ready

How to do it...

How it works...

Pivot Transformation

Unpivot Transformation

There's more…

Pivot Transformation; Working with the Advanced Editor

Text Analysis with Term Lookup and Term Extraction transformations

Getting ready

How to do it...

How it works...

There's more...

Excluding Terms

DQS Cleansing Transformation—Cleansing Data

Getting ready

How to do it...

How it works...

There's more...

The DQS Cleansing Component acts asynchronously

Advanced settings

Status Column

Fuzzy Transformations—how SSIS understands fuzzy similarities

Getting ready

How to do it...

How it works...

6. Variables, Expressions, and Dynamism in SSIS

Introduction

Variables and data types

Getting ready

How to do it...

How it works...

Using expressions in Control Flow

Getting ready

How to do it...

How it works...

DelayValidation

IsVariable

Real-world scenarios

Using expressions in Data Flow

Getting ready

How to do it...

How it works...

The Expression Task

How to do it...

How it works...

There's more...

Alternative method

Dynamic connection managers

Getting ready

How to do it...

How it works...

Dynamic data transfer with different data structures

Getting ready

How to do it...

How it works...

Other sources and destinations

Flat file to SQL server

SQL query to flat file

SQL server to SQL server on the same server

Excel to SQL server

Other DB engines with SQL server

Any other source and destination

7. Containers and Precedence Constraints

Introduction

Sequence Container: putting all tasks in an executable object

How to do it...

How it works...

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

Getting ready

How to do it...

How it works...

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

Getting ready

How to do it...

How it works...

ADO Enumerator

Variable Mappings

Type Cast in expressions in the derived column

Foreach Loop Container: looping through files using File Enumerator

Getting ready

How to do it...

How it works...

Foreach Loop Container: looping through data table

Getting ready

How to do it...

How it works...

DelayValidation

Precedence Constraints: how to control the flow of task execution

How to do it...

How it works...

Types of Constraints

Evaluation operation

Multiple constraints

8. Scripting

Introduction

The Script Task: Scripting through Control Flow

How to do it...

How it works...

ReadOnlyVariables

ReadWriteVariables

Script

Code description

Script results

Fire Events

The Script Component as a Transformation

How to do it...

How it works...

Script

See also

The Script Component as a Source

Getting ready

How to do it...

How it works...

CreateNewOutputRows

AddRow

See also

The Script Component as a Destination

How to do it...

How it works...

The Asynchronous Script Component

Getting ready

How to do it...

How it works...

SynchronousInputID

InputName_ProcessInput

There's more...

Script component methods

AcquireConnection

Log

PostExecute

PreExecute

ProcessInput

ReleaseConnection

9. Deployment

Introduction

Project Deployment Model: Project Deployment from SSDT

Getting ready

How to do it...

How it works...

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

Getting ready

How to do it...

How it works...

ISPAC file

Integration Services Deployment Wizard graphical user interface

Integration Services Deployment Wizard command-line utility

The Package Deployment Model, Using SSDT to deploy package

Getting ready

How to do it...

How it works...

File system

SQL Server

SSIS package store

Creating and running Deployment Utility

How to do it...

How it works...

Deployment Utility Ingredients

DTUTIL—the command-line utility for deployment

How to do it...

How it works...

There's more...

DTUTIL exit codes

Multiple packages deployment

Protection level: Securing sensitive data

How to do it...

How it works...

Sensitive data

Protection level types

Do Not Save Sensitive

Encrypt Sensitive with User Key

Encrypt Sensitive with Password

Encrypt All with User Key

Encrypt All with Password

Rely on Server Storage

There's more...

The ProtectionLevel property of a package

Protection level in project properties

Protection level in DTUTIL

10. Debugging, Troubleshooting, and Migrating Packages to 2012

Introduction

Troubleshooting with Progress and Execution Results tab

Getting ready

How to do it...

How it works...

Breakpoints, Debugging the Control Flow

Getting ready

How to do it...

How it works...

How to enable breakpoints

Breakpoints window

Breakpoint menu options

Monitoring windows

Breakpoints limitation in SSIS

Script breakpoint support

Handling errors in Data Flow

Getting ready

How to do it...

How it works...

Error columns and understanding them

Migrating packages to 2012

How to do it...

How it works...

Data Tap

Getting ready

How to do it...

How it works...

Create the execution

Create the Data Tap

Running the package

11. Event Handling and Logging

Introduction

Logging over Legacy Deployment Model

Getting ready

How to do it...

How it works...

Logging over Project Deployment Model

Getting ready

How to do it...

How it works...

There's more...

The SSISDB database

The SSIS dashboards

Some useful SSIS Catalog features

1 SSISDB

2 PacktPub

3 Projects

4 R02_Project Deployment Mode

Using event handlers and system variables for custom logging

Getting ready

How to do it...

Traditional approach

Project Deployment Mode

How it works...

There's more...

The traditional approach—Legacy Deloyment Model

The new approach—Project Deployment Model

Enriching default views

Get row counts

Custom reporting services reports

12. Execution

Introduction

Execution from SSMS

Getting ready

How to do it...

How it works...

There's more...

Logging level

Package validation

Execution and validation reports

Legacy package execution from SSMS

Execution from a command-line utility

Getting ready

How to do it...

How it works...

DTEXEC

There's more...

Execution with DTExecUI

Execution with SSIS catalog procedures

32-bit / 64-bit issue

Execution from a scheduled SQL Server Agent job

Getting ready

How to do it...

How it works...

There's more...

Running SQL Server Agent job under a proxy account

Creating an SQL Server job more easily

13. Restartability and Robustness

Introduction

Parameters: Passing values to packages from outside

Getting ready

How to do it...

How it works...

There's more...

Environment

Package configuration: Legacy method to inter-relation

Getting ready

How to do it...

How it works...

XML configuration file

SQL server

Environment variable

Registry entry

Parent package variable

There's more...

Indirect configuration

Configuration priority

Transactions: Doing multiple operations atomic

Getting ready

How to do it...

How it works...

Required

Supported

NotSupported

Checkpoints: The power of restartability

Getting ready

How to do it...

How it works...

CheckpointFileName

FailPackageOnFailure

CheckpointUsage

SaveCheckpoints

SSIS reports and catalog views

Getting ready

How to do it...

How it works...

There's more...

SSIS Catalog views

Simple catalog views

14. Programming SSIS

Introduction

Creating and configuring Control Flow Tasks programmatically

Getting ready

How to do it...

How it works...

Working with Data Flow components programmatically

Getting ready

How to do it...

How it works...

Executing and managing packages programmatically

Getting ready

How to do it...

How it works...

Saving packages

Loading packages

There's more...

Methods of the Application class

Set parameter's value programmatically

Creating and using Custom Tasks

How to do it...

How it works...

Signing the project

Adding assembly to GAC

There's more...

Creating custom Data Flow component

Creating UI Editor for custom object

15. Performance Boost in SSIS

Introduction

Control Flow Task and variables considerations for boosting performance

How to do it...

Using SSISDB Catalog

Progress Bar

Windows Performance Monitor

BIDS Helper

How it works...

Data Flow best practices in Extract and Load

Getting ready

How to do it...

Optimize Queries

OLE DB Destination

Data conversions

Update data into destination

How it works...

There's more...

Data Flow best practices in Transformations

Getting ready

How to do it...

Remove unused columns from the pipeline

Avoid unnecessary sorting in the pipeline

Lookup reference data

Make use of the Cache Transformation to store data in memory

How it works...

There's more...

Replace lookup by changing SQL statement at the source

Working with buffer size

How to do it...

How it works...

Working with performance counters

How to do it...

How it works...

Index