All About SSIS Variables - Part 1

• Download sample package - 527 B

Variables have been available in SSIS since first introduced in 2005. So what exactly are they for and what can you do with variables? Glad you asked!! Variables can be used for a number of different purposes:

1. Define the path of a package
2. Hold system information, such as error information
3. Provide a means to create custom logging
4. Hold result set(s) to iterate through

Let’s begin with a simple example such as using a variable to define the path of a package. Consider that you are having to execute a data flow task and based on the number of rows loaded will dictate what task should occur next. This is simple enough to using a variable and precedence constraint. The first step is to create a variable, which is done in the variable pane in SQL Server Data Tools:

Scope

The first thing to note from the variable pane is the scope. The scope defines where the variable will be available. For example, the scope for my variable “RowCount” is set to my package which I have named RowCount. This means that any task within the package, including child packages, will have access to the variable. It is best practice to ensure that the variable scope is limited to only where it is needed. This package will have a total of three tasks:

1. Data flow task that will take the data from the Adventureworks2012.Person.Person table through the Row Count transformation and to a flat file destination
2. A script task that executes if the row count is greater than 1,000
3. A script task that executes if the row count is less than 1,000

In this case, the scope is set to the package since all 3 tasks will need access to the variable. If the package contained other control flow tasks that did not require access to the variable, then scope could be limited by placing the three affected tasks in a sequence container and setting the scope to the container.

Data Type

The data type defines the type of object and the acceptable values that the variable can be held. Another important factor is to assign the most efficient data type for a variable. For example, if the variable will hold a numeric value between -128 and 128, the most efficient data type is SByte rather than UInt, which can hold a numeric value between 0 and 65,535. MSDN documents the data types here.

Value

This holds the value of the variable. This value can be changed within the package using script task, expression task, execute SQL task, as well as several others.

Expression

The expression provides a means of assigning a value to the variable using an SSIS expression. Again, this can be changed later within the package, but once package execution begins and the variable scope is invoked, the value or value from the expression provides the variable value until and if it is changed.

With the variable configured, now I will include my data flow task that will record the value of all rows that go from the source, Adventureworks2012.Person.Person, to the destination, to the flat file destination using a row count transformation. This is easy enough to do as once connecting the data source to the row count, you will be prompted for the variable that will be used to hold this numeric value.

Precedence Constraint

Keeping in mind the requirements, we now must define the path of execution based on the success of the data flow and the number of rows processed, which is now held within our variable. By connecting both script tasks using our On Success precedence constraint, we can then change both constraints to use Expression and Constraint, we can define one constraint to use the expression @[User::RowCount] > 1000 and the other @[User::RowCount] < 1000. This method will leave one path uncovered, that is if the RowCount == 1000. In this case, we are not concerned about that, so we will let it ride.

Each script task will be used to access the variable and display it in a message box and then reset the variable to 0. This requires that both tasks will need to have read and write access to the variable.

Public Sub Main()

MessageBox.Show("The row count was: " + Dts.Variables("RowCount").Value.ToString)
Dts.Variables("RowCount").Value = 0
MessageBox.Show("The row count was: " + Dts.Variables("RowCount").Value.ToString)

Dts.TaskResult = ScriptResults.Success

The final package in the control flow looks like this:

You will notice that the precedence constraints are green, meaning that the path will only be taken if the preceding task completes successfully. You will also note that there is an “fx” next to each precedence constraint which means that the constraint is also based on a precedence.

This is obviously a very simplistic example, but the first in the series, so we progress with each new post.

The sample package, completed in SSDT 2012, can be downloaded here.