All About SSIS Variables - Part 2: Objects

• Download source code - 19.9 KB

My last post looked at a simplistic example of using an SSIS variable in a row count transformation and then using the variable in a precedence constraint expression to direct the flow of the package. This post will focus on the unique object variable data type. The object data type is the ultimate base class in .NET. What exactly does that mean? Object is the root of the type hierarchy, which means that everything, at its core is an object. MSDN fully documents the object data type here as well as provides sample code here.

One of the more interesting values that can be held within an object is a full result set, which can then be treated similar to a collection. One thing that I want to point out is just because you can doesn’t mean you should. Quite often, I will be asked how a result set can be contained in a variable in SSIS and when I ask why, I am most often told that it would be used to do complex transformations on the results in the control flow. HOLD ON!! That is exactly what the data flow task is for!! In response to this, I am most often told that the transformations are most easily done in a script task. Once again, there is a script component in the data flow that can act as a source, transformation, and/or destination and such complex transformations should be kept in the data flow as a best practice as well as for performance considerations.

If I haven’t scared you away already, let me point out several other performance considerations. First, keep the scope of the variable limited to only what is needed, task, container, and only if necessary the package. The memory required to hold an object will be dictated by the value(s) that are stored in the variable so extra attention is required to limit the exposure to only what is required. Boxing and Unboxing can also carry performance costs, MSDN documents boxing and unboxing here.

Enough of the gloom and doom, let’s take a look at an object variable and how to work with it. To properly present the demonstration, let’s present the requirements of the sample package. A result set from an execute SQL task needs to be captured within the object variable that will consist of two columns, FirstName and LastName. The result set will then be iterated through to execute complex logic, in this case, just pass the values to a script task that will present the name in a message box(I know!! This is just a simple example though). This package will require three variables:

With the variables created, we can now begin to populate the Names variable with our execute SQL task. The task will need to set the Result Set property to full result set and the Connection Type will use an OLEDB connection to my localhost default instance connecting to the Adventureworks2012 database. The query is simplistic enough:

SELECT FirstName,
LastName
FROM Person.Person

We now need to define the Result Set settings in the task to specify that the results will be captured within our Names object variable. From within the Result Set tab, you must specify the zero based index value of the results to be stored in the Name property, since our query only returns a single result set this will be set to 0, and then the Variable Name of the SSIS variable that will be used to hold the result set, in this case Names.

Our package will now populate the Names variable with the results of our query and we now have several methods that we can work with this variable.

ForEach Container

The first way to work with an object is to use a Foreach container and set the Enumerator to a Foreach ADO Enumerator and define the ADO object source variable to our Names variable:

Now keep in mind that the Names is an object, very much like an array or collection, so we need to take both columns, FirstName and LastName, and place them in their own variables. This is done on the Variable Mapping page by again mapping the zero based index value of the ordinal position of the columns to the appropriate variables:

**I intentionally placed the LastName and FirstName out of order to demonstrate how the variable mapping is done based on the zero based index and not by the order in which the variables are mapped to the columns

Within the ForEach container, place a script task that has ReadWriteVariables or Read Only Variables set to both the FirstName and LastName:

You may ask why access is not given to the Names variable. The answer is that the ForEach container accesses the Names object variable and iterates though each row placing the FirstName column in the FirstName variable and LastName column in the LastName variable and passes those variables, one at a time, to the script task so access is not needed to the object.

The C# script task simply calls the MessageBox Show method to display the names one at a time:

MessageBox.Show("The name is: " + Dts.Variables[0].Value.ToString() +" " + 
                 Dts.Variables[1].Value.ToString());

Script Task

Another way to work with the variable is directly within a script task. Since the script task will now be iterating through the Names object variable directly, the variable needs to be assigned to the script tasks ReadWriteVariables or ReadOnlyVariables:

The C# script task first creates an OleDbAdapter and a data table and calls the OleDbAdapters Fill method to populate a data table with the Names object variable. Once the data table is populated, a foreach loop is used to iterate through the results and display the results using the MessageBox Show method.

 //Populate a data table with the Names Variable
				OleDbDataAdapter A = new OleDbDataAdapter();
				System.Data.DataTable dt = new System.Data.DataTable();
				A.Fill(dt, Dts.Variables["User::Names"].Value);
// Iterate through the data table
foreach (DataRow row in dt.Rows)
{
string FirstName;
string LastName;
object[] array = row.ItemArray;
FirstName = array[0].ToString();
LastName = array[1].ToString();
MessageBox.Show("FirstName=" + FirstName + " AND LastName=" + LastName);

The sample package outlined can be downloaded here.