Udostępnij za pośrednictwem

Using SSIS Script Task to mimic DTS 2000 Execute Package Task variable passing behavior

  • Artykuł

In DTS 2000, the execute package task offers the ability to pass variables into the child package, and retrieve result values from the child package into global variables after execution. For 2005, this mechanism has been replaced by parent package configurations. See links for more detail:

https://msdn2.microsoft.com/en-us/library/ms345179.aspx

https://sqljunkies.com/WebLog/knight_reign/archive/2004/12/03/5366.aspx

https://blogs.conchango.com/jamiethomson/archive/2005/09/01/2096.aspx

Parent package configurations will handily pass variables into the child package. Things get a little tricky for passing result values back. You can update parent package variables directly from the child package, but this may not work for you. If the child package has an identically named variable, it will hide that in the parent package, and the parent package variable won't get updated. If the child package does not have a matching variable, then the child package won't validate and run when invoked separately, making management more difficult.

One way to pass variables back from child packages is by using script to invoke the child package. I have constructed a pair of packages to illustrate this.

The first is called "InvokedByScript.dtsx". It contains two variables: PassedIn and PassedBack. Both are integers. It contains a single script task. On the script tab of the Script Task Editor, I configured ReadOnlyVariables to have the value "PassedIn", while ReadWriteVariables is set to "PassedBack". In the actual script code, I added one line:

Dts.Variables(

"PassedBack").Value = 1 + Convert.ToInt32(Dts.Variables("PassedIn").Value)

I saved the package to C:\temp.

The second package is called "InvokesInvokedByScript.dtsx". It too contains two variables, PassedIn and PassedBack (both integers), as well as a single script task with ReadOnlyVariables set to "PassedIn" and ReadWriteVariables set to "PassedBack". Added script code looks like this:

Dim pkg As String = "C:\temp\InvokedFromScript.dtsx"
Dim app As Application = New Application()
Dim p As Package = app.LoadPackage(pkg, Nothing)
p.Variables("PassedIn").Value = Dts.Variables("PassedIn").Value
p.Execute()
Dts.Variables("PassedBack").Value = p.Variables("PassedBack").Value

The code loads the child package, sets a child variable, executes the child package, then updates the parent package variable from the child package variable's value. You can set PassedIn to any value, then set a breakpoint in this code, and use the watch window to observe the four variables as they change during execution.