Greetings everyone, and thank you for taking the time to look at this blog post. In this post I will be showing you how to use the object variable to make packages dynamic. Specifically I will be incorporating this with a 3rd party component that we here at Pragmatic Works do offer, Task Factory. The core of what makes this example 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 Varibale there is no way to input a value, it simply states “System.Object” as seen below:
**Click to Enlarge**
No need to worry this is perfectly normal. What happens is that you can load up 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 that package is done it does not retain that 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 a brief description of what the object variable can do, let’s see it in action in the short video below. I hope you enjoy. For more blogs related to SSIS and Task factory you can visit me here.