Using Object Typed Variables in SSIS

Note: This will be the first post in a short series on using object typed variables (also known as SSIS object variables) in SQL Server Integration Services.

When defining variables in SSIS, the ETL developer has several data type options to choose from depending on the information to be stored in each variable.  Included in the options are String, Boolean, Char, DateTime, and several flavors and sizes of Int.  However, there’s another variable data type that is very handy but also underutilized: the object data type.

Why Object Variables?

SSIS object variables are easily the most flexible type of storage structure in Integration Services.  Data types such as Int, String, and DateType are designed to store just one type of data; however, an SSIS object variable can store almost any type of data, even information that can’t otherwise be represented in Integration Services.  In most cases, SSIS doesn’t even have to be configured to know what type of data you’re storing in an object-typed variable – usually, it can simply pass the value along the wire as a bunch of bits without knowing or caring what’s in there.

When considering the use of an SSIS object variable, I’ll give the same disclaimer that I give for using script tasks/components in SSIS: Just because you can doesn’t mean you should.  If a native data type will work to store any possible value for a particular variable, by all means, don’t complicate your code by adding unnecessary moving parts.  Use Object typed variables only when a native type won’t do – otherwise, stick to the well-worn path.

Common Uses

There are a few cases that come to mind that lend themselves to using SSIS object variables:

• Iterating over a result set.  This is probably the most common and well-documented use of object variable, as well as the easiest to implement as it requires no manual coding.  This pattern will allow you to retrieve a set of data from a database, and then perform some operation for each row in that result set.  I’ll work through the mechanics of how to do this in my next post.
• Handling binary data.  A common example of this is extracting binary data from or writing binary data to a VARBINARY field in a relational database.  If you need an interim storage mechanism in which this binary data should be stored, and Object variable can be a good solution.  In this case, the ETL pipeline doesn’t necessarily need to be aware of what is stored in the Object typed variable .
• Creating or consuming binary data in SSIS.  Let’s say you need to either generate or process binary data as part of your ETL.  For example, you might need to retrieve a JPEG or PNG file from the file system and write it into a relational database, or retrieve a binary object from a database and process the various elements of that object.  By storing said data in an SSIS object-typed variable, you can directly write to or read from this variable within your code.

Risks and challenges

Naturally, with a construct as flexible as an SSIS variable, there are a few challenges to be aware of when considering when and how to use objects in your SSIS packages.  Among the risks:

• Some coding required.  In many cases, especially when you’re processing the information contained in the SSIS object variable (as opposed to simply passing the value through from a source to a destination), you’re going to have to write some code to address that object.
• SSIS expressions not allowed.  Because they are designed to store a variety of information structures, object-typed variables cannot be used in an SSIS expression.  Even if the underlying data stored in the variable is of a type that could be stored in a native SSIS type, attempting to add an Object typed variable to an SSIS expression will throw an error.
• Debugging challenges. If you overflow an Int32, or happen to truncate a string in SSIS, you’ll get a (mostly) friendly error message telling you what went wrong.  Often, when dealing with SSIS object variables, you don’t get that luxury.

Conclusion

SSIS object-typed variables allow a great deal of flexibility when dealing with atypical data structures in the ETL cycle.  Although they are not as commonly used as simple native types, SSIS object variables can make otherwise difficult ETL tasks easier.

In my experience, I’ve found that object-typed variables are sometime avoided in SSIS packages simply because they’re misunderstood or believed to be too difficult to use.  In the next few posts in this series, I’ll illustrate how the SSIS object variable can be leveraged in your SSIS package by demonstrating a few use cases where they are appropriate.