A common feature of DTS packages is that they can be dynamically edited at runtime. This is accomplished by using ActiveX script code to alter the DTS package which is in itself an instance of the DTS Object Model. Once you have access to that object model (which in script is done by using the line "DTSGlobalVarables.Parent") you can navigate over the variables, steps and tasks of that package changing them accordingly.
DTS developers used this ability to allow them to do things such as:
- Altering workflow based on certain conditions
- Implementing looping within a package
- Dynamically changing a data pump's source, destination & column mapping using metadata available at runtime. This technique is used to build generic loading packages that can load from any data source as long as the metadata is known.
#1 & #2 here are achieved much more easily in SSIS by use of expressions on precedence contraints and the For/Foreach loop containers. Not the case with #3 however.
You cannot do this (i.e. change the metadata of a data-flow at runtime) in SSIS. DTS developers may be reading this aghast at what I'm saying but I'm afraid its the truth. Yes there is an SSIS Object model, just as there is the DTS Object Model. Yes, you can reference parts of that object model from script code. But you cannot change it.
There are some other options available to you though:
- Although an SSIS package's object model cannot be changed at runtime by script code you can still build dynamic packages. Variables (and the use of them) have a much bigger story to tell in SSIS than they did in DTS. Variables now have scope, they can be evaluated as an expression rather than just storing an explicit value, they can have namespaces, they can raise an error when altered and, in conjunction with property expressions, can be used to dynamically alter tasks at runtime. A typical use of a variable with a property expression would be to build dynamic SQL statements in the Execute SQL Task. The screenshot below shows a very simplistic example of doing this:
Here you can see how a SQL statement has been dynamically generated using a value stored in the "KeyValue" variable.
Property expressions can be used to change any property of a workflow task and are therefore very useful indeed. What they cannot do is change properties of data-flow components hence they cannot be used to do the same as described in #3 (above)
- If you want to dynamically affect the column metadata of a data-flow at runtime you're outta luck, it can't be done. However all is not lost. You have the option of using code to instantiate a new package at runtime or change another existing package and execute that package within the within the confines of your curently executing package. I haven't attempted to do this yet but plan to at some point. Keep checking this blog to see how I get on.
- Jamie