Wednesday 29 December 2010

Passing variables from a Parent Package To a Child

Case
Occasionally you need to pass some value from the parent package to the child package. Of cource you could temporary store it in a database or file, but that could be a little too much for a single value.

Solution
The easiest solution is to store the value in a variable in the parent package and use a script task in the child package to copy it.

1) Create parent variable
Create a variable in your parent package. Right click in the Control Flow if the variables are not visible. I used a string variable named FilePath in this test case to store some filepath.
Create new variable, think about the scope.











2) Create Execute Package Task
Drag an Execute Package Task in your Control Flow and configure it to start your child package.
Execute Package Task



















3) Create child variable
Goto to your child package and create a variable, but be sure not to use the same name as in the parent package. Otherwise it won't work! I used LocalFilePath in this case.


4) Script task
Drag a Script Task in the Control Flow of your Child package. Choose your script language (I will show both C# and VB.net for this short script) and select your Child package variable as a ReadWriteVariable and type (you can't select it) your Parent package variable as ReadOnlyVariable. The Scope (User::) is optional.
Language and Variables




















5) The Script
The simple version of this script is only 1 row of code. I will show the more advanced version at a later time.
' VB.Net code
Public Sub Main()
    ' Fill local variable with value from parent variable
    Dts.Variables("User::LocalFilePath").Value = Dts.Variables("User::FilePath").Value

    Dts.TaskResult = ScriptResults.Success
End Sub
// C# code
public void Main()
{
    // Fill local variable with value from parent variable
    Dts.Variables["User::LocalFilePath"].Value = Dts.Variables["User::FilePath"].Value;

    Dts.TaskResult = (int)ScriptResults.Success;
}
That's it. The child variable is now filled with the value of the parent variable.
The end result













The SSIS solution can be downloaded here.

Note: the child package cannot run by itself, because it will fail finding the parent variable. A simple solution is to change the Constraint behind the Script task from Success to Completion (see blue line in last picture).

Update: All the roads lead to Rome. So here is another road/solution without .Net Coding

2 comments:

  1. Hi,
    I had followed the same steps , But I am unable to read parent variable in Step4 to set as readonly variable..
    I think we need to use parent package variable in SSISconfigurations
    Let me know if I am wrong..

    ReplyDelete
  2. @bru: in the Script Task of the child package you have to write/type the name of the parent package variable name. Unlike the variable of the child package, you can't select it, because only at runtime the variable of the parent package is known by the child package.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.

Related Posts Plugin for WordPress, Blogger...