Variables and Expressions in SSIS: A Complete Guide

 Variables and expressions are key to making SQL Server Integration Services (SSIS) packages dynamic, reusable, and adaptable to runtime conditions. Below is a detailed explanation with practical examples.

1. Variables in SSIS

What Are Variables?

Variables store values that can be used across the SSIS package, such as file paths, SQL queries, flags, or counters.

Types of Variables

Type	Scope	Example
System Variables	Built-in (read-only)	System::PackageName, System::StartTime
User Variables	Custom (read/write)	User::SourceFilePath, User::RowCount

Variable Properties

• Name: Unique identifier (e.g., User::FileName).

• Data Type: String, Int32, DateTime, Boolean, etc.

• Value: Default value (can be changed at runtime).

• Scope: Defines where the variable is accessible (Package, Container, or Task level).

How to Create a Variable?

1. Open Variables Window (SSIS > Variables or Ctrl + \).

2. Click Add Variable and set:

   • Name: User::SourceFolder

   • Data Type: String

   • Value: C:\Data\

---

2. Expressions in SSIS

What Are Expressions?

Expressions are formulas that dynamically compute values at runtime using:

• Variables (@[User::VarName])

• Operators (+, -, ==, &&)

• Functions (GETDATE(), SUBSTRING(), REPLACE())

Where Can Expressions Be Used?

1. Variable Expressions (Update a variable dynamically).

2. Property Expressions (Modify task properties at runtime).

3. Precedence Constraints (Conditionally execute tasks).

SSIS Expression Language Syntax

Component	Example
Variable Reference	@[User::SourcePath] + "file.txt"
String Concatenation	"Server=" + @[User::ServerName]
Date Functions	(DT_WSTR,4)YEAR(GETDATE()) → "2024"
Conditional Logic	@[User::IsFullLoad] ? "TRUNCATE TABLE X" : "SELECT * FROM X"

---

Error Handling and Logging in SSIS

 

1. Error Handling in SSIS

A. Precedence Constraints (Success, Failure, Completion)

• Control the flow of tasks based on execution status:

  • Green (Success) – Proceed if the previous task succeeds.

  • Red (Failure) – Execute if the previous task fails.

  • Blue (Completion) – Execute regardless of success/failure.

B. Event Handlers

• Execute custom logic when specific events occur:

  • OnError – Runs when a task fails.

  • OnWarning – Runs when a warning occurs.

  • OnTaskFailed – Runs when a task fails.

  • OnPostExecute – Runs after a task completes successfully.

Example:

• Log errors to a database or file when OnError is triggered.

C. Error Output in Data Flow

• Configure error outputs for transformations and destinations:

  • Ignore Failure – Skip the error and continue.

  • Redirect Row – Send failed rows to an error output.

  • Fail Component – Stop execution on error.

Example:

• Redirect bad rows to an error table for later analysis.

D. Transactions & Checkpoints

• Transactions: Use TransactionOption to roll back on failure.

• Checkpoints: Restart packages from the point of failure.

SSIS variable interview question

What are variables and what is variable scope?

variables are extremely important and are widely used in an SSIS package.A variable is used to store values. A variable is a named object that stores one or more values and can be referenced by various SSIS components throughout the package’s execution.here are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.

Realtime scenario for ssis variable:

We have created a SSIS Package that takes the files from Source Folder and move them to Archive Folder. While it move them, It also add Time Stamp to file. Here is the blog post for the Package  .In this post we want to watch the values of the variables at run time. This can be helpful when we are getting some errors and we are not sure if the value of variables are changed while executing SSIS Package Or our expressions are evaluated correctly that we have written on one or more variables. By looking at the value of variables, It will help us to debug our package.

Solution:

We will be using Break Point in SSIS Package to view variable values at run time. Here are the variable I have used in package. I have changed the values for VarArchiveFullPath and VarSourceFullPath variables.

Expressions are written on these variable those should evaluate at run time and change the values of these variable.

Step 1: 

Right Click on File System Task and go to Edit Breakpoints

Step 2: 

Choose the Break Condition.

Step 3:

Execute your SSIS Package

Step 4:

To view the values of your variable Go to Debug--> Windows--> Locals

Let's see the values of variables win Locals window

We can see that the values are changed according to the expression and we can debug if these values are correct according to our expectations, if not then we can go back to expressions and change them. 

Copy All Tables from IBM DB2 schema to SQL Server

I am trying to come up with the best and most efficient way to copy more than 700+ tables from an IBM DB2 iSeries schema to SQL Server. I have tried the followings:

1. I have exported all the 700+ tables into csv files but I am not able to upload them all in one batch to SQL. I can upload one table at time but it too time consuming

2. Created SSIS package to directly copy table form IBM to SQL but that's gain working for only one table at a time

Any recommendations as what tool(s) or methods to use for copying/uploading multiple tables from IBM to SQL?

ANS:
If you create a table that lists all the tables/files to be imported, and a script that takes a filename and imports the file (possibly with BULK INSERT), you could run multiple copies of that script to do parallel imports.  A flag bit in the table could be used to mark which tables have been chosen by one of the script copies.

Conditional Split Transformation in SSIS

Conditional Split Transformation in SSIS:

Why Conditional Split Transformation Task is Use ?

Conditional Split Transformation task use when want to direct subsets of data to different locations, such as sending centrally collected data to appropriate branches or within the dataflow when we want to apply different business rules to certain subsets of the data, before merging this Dataset.

This is most useful transformation in Data Flow Task where we can traverse through different condition.

In Conditional Split Transformation incoming data can be split into multiple outputs as per the conditions specified. It has a default output too that handles all the rows that do not match to any conditions

How Conditional Split Transformation Task is look like ?

Features of Conditional Split Transformation Task

When we need to perform one to many operation we often use conditional transformation

Lets consider we need to perform various operation in same database

How to use Conditional Split Transformation Task ? and Demonstration of using Conditional Split Transformation Task ?

This is as simple as derived column transformation which have already discuss ..

Just drag out this task from Data Flow Tasks

Once we are ready to use we can configure as shown in following diagram

Various Properties For each Conditional Split Transformation Task ?

Following are various properties for Split Transformation

Reference link For Conditional Split Transformation Task

http://www.dotnetfunda.com/articles/article1214-conditional-split-in-ssis.aspx

For Each Loop Container in SSIS

For Each Loop Container in SSIS:

Why For Each Loop Container Task is Use ?

• For Each Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics.
• In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• The for each loop container acts as a repeating control flow in a package. Its operations are similar to work of For each keyword in any advanced programming language. We have a definite type of enumerator for each type of objects.
• Loop implementation in the For Each Loop Container is similar to the Foreach looping concept in various programming languages.

The Foreach Loop container defines a repeating control flow in a package. The loop implementation is similar to Foreach looping structure in programming languages. In a package, looping is enabled by using a Foreach enumerator. The Foreach Loop container repeats the control flow for each member of a specified enumerator.

Enumerator is nothing but an iterator where an object that enables a programmer to traverse a container

SQL Server Integration Services provides the following enumerator types:

1. Foreach File Enumerator: It enumerates files in a folder. The plus point here is it can traverse through subfolders also.
2. Foreach Item Enumerator: It enumerates items in a collection. Like enumerating rows and columns in an Excel sheet.
3. Foreach ADO Enumerator: Useful for enumerating rows in tables.
4. Foreach ADO.NET Schema Rowset Enumerator: To enumerate through schema information about a data source. For example, to get list of tables in a database.
5. Foreach From Variable Enumerator: Used to enumerate through the object contained in a variable. (if the object is enumerable)
6. Foreach NodeList Enumerator: Used to enumerate the result set of an XML Path Language (XPath) expression.
7. Foreach SMO Enumerator: It enumerates through SQL Server Management Objects (SMO) objects.

How For Each Loop Container Task is look like ?

This is Second task in tab itself !!

Drag this out in your development plane

Features of For Each Loop Container Task

The following diagram shows a Foreach Loop container that has a File System task. The Foreach loop uses the Foreach File enumerator, and the File System task is configured to copy a file. If the folder that the enumerator specifies contains four files, the loop repeats four times and copies four files.

SSIS For Loop Container Task

SSIS For Loop Container Task:

Why For Loop Container Task is Use ?

• Name of the task itself explains most of of it !! For Loop container is falls under container and looping tasks
• Use containers like the For Each Loop and For Loop to execute a set of tasks multiple times.
• For example, you can loop over all the tables in a database, performing a standard set of operations like updating index statistics. 
  In short when we have to iteratively execute set of task we will insert all those task under For Loop Container and set the values accordingly
• For loop task is the looping implementation of a task and also This task will evaluate an expression and loops through the process and until the evaluation goes to False.

The For Loop container uses the following elements to define the loop:

1. An optional initialization expression that assigns values to the loop counters.
2. An evaluation expression that contains the expression used to test whether the loop should stop or continue.
3. An optional iteration expression that increments or decrements the loop counter.
   

SSIS Balanced Data Distributor transform

SSIS Balanced Data Distributor

Transform:

Microsoft download center has a new download available that would be of interest to many SSIS professionals. Its a new transform named Balanced Data Distributor transform, which takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. Below mentioned is the description of this transform as mentioned on the download page:

"Microsoft® SSIS Balanced Data Distributor (BDD) is a new SSIS transform. This transform takes a single input and distributes the incoming rows to one or more outputs uniformly via multithreading. The transform takes one pipeline buffer worth of rows at a time and moves it to the next output in a round robin fashion. It’s balanced and synchronous so if one of the downstream transforms or destinations is slower than the others, the rest of the pipeline will stall so this transform works best if all of the outputs have identical transforms and destinations. The intention of BDD is to improve performance via multi-threading. Several characteristics of the scenarios BDD applies to: 1) the destinations would be uniform, or at least be of the same type. 2) the input is faster than the output, for example, reading from flat file to OleDB. "

Considering the test performed on this transform, from the results it seems that performance of this transform is a little better over other transforms like Script and Conditional Split. But it should also be taken into consideration that this transform buffer by buffer to its output ends, and the other two checks the data based on the specified logic and then divides the data. Its a nice transform to have in your existing SSIS toolbelt.

The best use of this transform that I can think of is when your input is extremely fast, and you have replicated blocks of logic to keep up the pace with the incoming data flow. In such a case BDD acts as a distributor bridge pumping data to all logic pipelines. But if data is to be dissected conditionally, Conditional Split would be the option again. The speed of distribution harnessed by this transform seems to be due to two reasons: 1) No distribution of data conditionally, just pass buffer by buffer 2) Multithreading architecture.

Certain curious questions the my mind raises are:

1) Denali is already in CTP mode and more CTPs are expected to come. Then why this transform has been released separately at this time and so silently?

2) Will this transform be a regular transform available with Denali, or would it remain a mysterious separated out transform?

3) This transform is available only for SSIS 2008 and SSIS 2008 R2, not SSIS 2005. Why ? Actually it's SSIS 2005 that needs more help with transforms like this, where it would be a value addition to customers who have already made investments in SSIS 2005 !

Let's look forward to the next CTP of Denali to checkout whether this transform would have a seat in SSIS Denali.