Wednesday, June 28, 2006

Self Modifying Packages in SSIS?

"Self Modifying Packages in SSIS?
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.
The Script
Imports System.Collections.Specialized
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()
th.Properties('TablesList').SetValue(th, sc)
application.SaveToXml(packagename, package, Nothing)
Dts.TaskResult = Dts.Results.Success
End Sub
This is some quick and dirty code that uses the package objec"
By Kirk Haselden