Many visitors to the SSIS forum ask if it is possible to modify an executing package using a Script Task within that package; this was a fairly common scenario in the predecessor Data Transformation Services (DTS) so its not surprising that people ask this. The simple answer is “no” and in days of yore I would direct people to a blog post written by Kirk Haselden (former Development head honcho for SSIS) entitled “Self Modifying Packages in SSIS?” in which he explained the why and wherefores of this scenario. That original blog has since disappeared never to be seen again however I did stumble upon it on a plagiarist’s blog earlier this week and figured it might be better if it were re-published somewhere where (a) it had Kirk’s permission (b) was a bit more relevant and (c) was more likely to stick around for a while. Hence, I contacted Kirk and asked him if I could re-publish that blog post here on SQLBlog; he agreed and so you can find the entirety of that blog post below. Bear in mind that this was written for SSIS version 1 however it is still very much relevant today.
Without further ado…
Self Modifying Packages in SSIS? by Kirk Haselden
Yeah, thought that might get your attention.
First, packages cannot modify themselves during execution. There is no package pointer passed to the tasks any longer, so you can’t traverse the package object model with the script task any longer. That is, you can’t traverse the package object model for package in which the script task resides. You CAN however open and modify other packages, including those that the parent package is about to execute with the Execute Package task. This is the same model as self modifying packages in DTS, except it’s safer because you’re not attempting to change the package as it is running.
Here’s the script from a chapter of my book that shows you how to modify a Transfer Objects Task to move some tables. There is no error handling code for clarity, bla bla bla. The usual caveats apply, check for errors, handle exceptions.
Public Sub Main()
Dim application As Microsoft.SqlServer.Dts.Runtime.Application = New Application()
Dim packagename As Object = Dts.Connections(“Tables”).AcquireConnection(Nothing)
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = application.LoadPackage(packagename.ToString(), Nothing)
Dim th As TaskHost
th = package.Executables(“TablesToMove”)
Dim sc As StringCollection = New StringCollection()
application.SaveToXml(packagename, package, Nothing)
Dts.TaskResult = Dts.Results.Success
This is some quick and dirty code that uses the package object model to open the child package and modify it.
In the parent package there are a couple of variables I use, Tables1 and Tables2, to change the table I want to move. Now, you can change this how you want. Stick them all in a comma delimited string if you want and parse them out in script or stick them in a variable one-by-one with a ForEach Nodelist Enumerator. It doesn’t really matter how you get the table names into the script.
The Package Connection
The script uses the same Connection Manager as the Execute Package task to find the name of the package to open and modify.
Modifying the Package
Now the property type for the Transfer Objects Task for the TableList is a StringCollection. So, I create a StringCollection in script and populate it. Then I open the child package, the one that will be moving tables, and set the property on the Transfer Objects Task and save the package again.