Colorado speaking tour

I’m happy to announce that I’ll be doing a tour of the 4 SQL Server user groups in central Colorado next week.  I’ll be speaking at these four user groups:

Monday (3/18): Northern Colorado Database Professionals

Tuesday (3/19): Boulder SQL Server User Group

Wednesday (3/20): Colorado Springs SQL Server User Group

Thursday (3/21): Denver SQL Server User Group

At each of these user group meetings, I’ll be delivering a session entitled “When ETL Goes Bad: Handling Errors and Anomalies in SSIS”.  This is the second such tour that I’ve done: two years ago I made trip up there and spoke at 3 of these user groups.  It’s always a pleasure to visit this area – the Colorado SQL community has a lot of great people, and I’ve got several friends in the area that I hope to see.

This will be a working trip, too.  I’ve got a client in the Denver area that I’ll be working with during the week, so I expect it to be a very packed week.  I’m taking the day off on Friday and am staying over until Sunday morning, so I should have two full days to squeeze in some skiing while I’m there.

If you’re in the area, please stop by one of the user group meetings and say hello.  And if you’re up for some skiing on Friday or Saturday, let me know Smile.

Business Intelligence Projects now supported in Visual Studio 2012

Since the release of Visual Studio 2012, business intelligence developers have been limited in how much they could use this tool due to the fact that it did not support BI project types (SSIS, SSAS, and SSRS).  Today, that limitation is now gone with the release by Microsoft of SQL Server Data Tools – Business Intelligence for Visual Studio 2012.  With this release, BI professionals may now fully move onto Visual Studio 2012 for SQL Server 2012 project initiatives.

You can read more about this release on the SSRS team blog and SSAS team blog, or download the code from the Microsoft website.

Using Object Typed Variables in SSIS

Note: This will be the first post in a short series on using Object typed 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?

Variables with a data type of Object are the most flexible variables in SSIS.  Data types such as Int, String, and DateType are designed to store just one type of data; however, an Object typed SSIS 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 Object typed variables in SSIS, 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 Object typed variables in SSIS:

  • 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 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 Object typed variables, 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 Object typed 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 Object typed variables, you don’t get that luxury in SSIS.


Object typed variables in SQL Server Integration Services 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, Object typed 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 Object variable can be leveraged in your SSIS package by demonstrating a few use cases where Object typed variables in SSIS are appropriate.