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# codeThat's it. The child variable is now filled with the value of the parent variable.
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;
}
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