All About SSIS Variables Part 1

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:

variablepane

 

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 insure 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 -128and 128 the most efficient data type is SByte rather than UInt, which can hold a numeric value between o 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 other.

 

Expression

The expressions 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.

DataFlowCount

 

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.

precedence

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.

ScriptVariable

I will use VB.NET in each script task using the following code:
<style=”font-size: 12px;”>
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:

FinalPackage

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.

 

Leave a Reply


Hit Counter provided by Sign Holders