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()
sc.Add(Dts.Variables(
"Tables1").Value.ToString())
sc.Add(Dts.Variables(
"Tables2").Value.ToString())
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 object model to open the child package and modify it.
Table Names
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.
Here's the parent package with the script task and the Execute Package task.
This is the script task UI. Notice the variables that I'm referencing.
Finally, this is the child package, pretty simple. Has just the Transfer Objects task.
Notice that the tables selected to be moved in the Transfer Objects Table are the same as those specified in the variables on the parent package.
Easy right!
Universe.Earth.Software.Microsoft.SQLServer.IS.KirkHaselden