Implementing Control Flow in an SSIS Package

• Show/Hide

• Move

• Add annotations to provide documentation

Using Multiple Packages

• Create reusable units of workflow

• Run multiple control flows in parallel

• Separate ETL workflows to fit data acquisition windows

Pkg1

Pkg2

Execute Package tasks

Pkg4

Pkg3

Lesson 2: Creating Dynamic Packages

• Variables

• Parameters

• Expressions

Variables

• User Variables:

• Variables created by an SSIS developer to hold

dynamic values

• Defined in the User namespace by default

• Defined at a specified scope

• System Variables

• Built-in variables with dynamic system values

• Defined in the System namespace

User::fName

System::StartTime

Name: fName

Data Type: String

Value: MyFile.csv

Scope: Package

Name: StartTime

Data Type: DateTime

Value: When the package

started running

Parameters

• Project parameters

• Accessible from any package in the project

• Package parameters

• Exist only at the package level

Project::folderPath

Default Value: "D:\MyFiles\"

Package1

Project

Package::dbConnStr

Default Value: "Server=localhost…"

Package2

Package::ftpSrvr

Default Value: "ftpsrv01"

Expressions

• Used to set values dynamically:

• Properties

• Conditional split criteria

• Derived column values

• Precedence constraints

• Based on Integration Services expression syntax

• Can include variables and parameters

• Can be created graphically by using Expression

Builder

@[$Project::folderPath]+[@User::fName]

Lesson 3: Using Containers

• Introduction to Containers

• Sequence Containers

• For Loop Containers

• Foreach Loop Containers

Introduction to Containers

• Task Containers

• Sequence Containers

• For Loop Containers

• Foreach Loop Containers

Package

Sequence

For Loop

Task

Task

Foreach Loop

Task

Task

Sequence Containers

• Define a control flow subset

• Enable you to manage properties for multiple

tasks

• Create a scope for variables, transactions, and

precedence

Sequence Container

Task 1

Task 2

Task 3

Task 4

For Loop Containers

• Implement iterative control flow

• Similar to a C# For loop

• Initialization expression

@Count = 0

• Evaluation expression

@Count < 4

• Iteration expression

@Count = @Count + 1

Iterator variable (Count)

For Loop

@Count = 0

@Count < 4?

No

Yes

Task

@Count = @Count + 1

Task

Foreach Loop Containers

Iterate through an enumerated collection

Foreach Loop

Task

Enumerator variable

(for example, file name)

• ADO

• Rows in a recordset

• ADO.NET Schema Rowset

• Objects in a database schema

• File

• Files in a folder

• Variable

• Elements in an array variable

• Item

• Enumerated property values of an item

• Nodelist

• Nodes in an XML document

• SMO

• SQL Server Management Objects

Lab A: Implementing Control Flow in an SSIS Package

• Exercise 1: Using Tasks and Precedence in a

Control Flow

• Exercise 2: Using Variables and Parameters

• Exercise 3: Using Containers

Estimated Time: 60 minutes

Lab Scenario

You are implementing an ETL solution for

Adventure Works Cycles and must ensure that the

data flows you have already defined are executed

as a workflow that notifies operators of success or

failure by sending an email message. You must

also implement an ETL solution that transfers data

from text files generated by the company’s

financial accounting package to the data

warehouse.

Lesson 4: Managing Consistency

• Configuring Failure Behavior

• Using Transactions

• Using Checkpoints

Configuring Failure Behavior

• Use properties to control failure

propagation:

• FailPackageOnFailure

• FailParentOnFailure

• MaximumErrorCount

Package

Sequence





Package

Sequence



X

Package

Sequence

X



Package

Sequence

X

X

X

Task

X

Task

X

Task

X

Task

FailParentOnFailure: False

FailPackageOnFailure: False

True

False

False

True

True

True

Using Transactions

• Set the TransactionOption property of a

task, container, or package:

• Required

• Supported

• NotSupported

Supported

Not

Supported

Supported

Required

Supported

Tx

Tx

Tx

Execute

Package Task

Supported

Supported

Tx

Tx

Not

Supported

Tx Enlisted in

transaction

Using Checkpoints

• Restart failed packages without repeating

previously successful tasks

• Enable checkpoints by setting package

properties:

• CheckpointFileName

• CheckpointUsage

• SaveCheckpoints

X



X







Checkpoint File

Package Execution

Package Re-execution

Lab B: Using Transactions and Checkpoints

• Exercise 1: Using Transactions

• Exercise 2: Using Checkpoints

Estimated Time: 30 minutes

Lab Scenario

You are concerned that the Adventure Works ETL

data flow might fail, leaving you with a partially-

loaded staging database. To avoid this, you intend

to use transactions and checkpoints to ensure

data integrity.