Greetings everyone, and thank you for taking the time to look at this blog. This is my first post and I hope I’m able to answer some of your questions about building packages in SSIS. In this post I will be showing you how to use the object variable to make packages dynamic while using Task Factory, a SSIS product by Pragmatic Works.
The core to what makes this work is how the object variable stores data which is known as a result set. When working in SSIS you will notice that when you create an Object Variable there is no way to input a value, it simply states “System.Object” as seen below.
No need to worry, this is perfectly normal. What happens is that you can load rows of data inside this variable using the native Result Set Destination component and they will all be available in the variable. The trick is how to get this data out and in the format you would like, that’s where the For Each Loop container comes into play. Once you have an object variable populated you can use this component to parse through each row in the Result set and send those values to user defined variables to be used in your SSIS package.
The important thing to note is that the object variable is only populated at run time, which means when a package is done it does not retain data. Also how the object variable holds the data doesn’t allow you to directly use the object variable itself in the development process. Now that we have brief description of what the object variable can do let’s see it in action in the short video below, I hope you enjoy.
Manuel comes from an 8 year hospitality career where customer service was number one. He is currently a product support engineer with Pragmatic Works and works closely with our line of products including Task Factory, BI xPress and DOC xPress. Manuel is rapidly becoming an expert in SSIS and all things SQL Server.
For more information about Manuel and blogs related to SSIS, please visit his blog here.