SSIS 2012 pass values from child package to parent

Question

i'm using the new project deployment model.

I have a master package called ETL. The first thing my ETL package does is run a package called get SFTP files as shown.

Within Get SFTP files a foreach loop gets the ClientID. HOW do i pass this value back to the parent package ETL??? To do inserts etc.

Answer 1

I am still working on migrating SSIS packages from previous versions into SSIS 2012, so the information given here may not work with 2012 version.

In previous versions, we found out that the child package has visibility (and can change) SSIS Variables values in the Parent Package that are in the scope (usually the Parent Package's Global Variables) of the Child package at run time.

This is done by developing the child package as a stand alone package using the same name of the Parent Package variables that the child package needs to change at run time.

When the Child Package is fully developed and tested as stand alone and before deploying it, the variables with the same name of the Parent Package's variables should be deleted from the Child Package and set the Delay Validation property of the Child Package to true to avoid validation errors at deployment time.

At Run Time, when the Parent Package calls (or runs) the Child Package, in fact the Child Package becomes a component of the Parent Package and therefore can reference and change variables in the Parent Package within the scope of the Child Package.

Hope this does not sound very confusing :)

Answer 2

The best way to do this is to include your child and parent packages in the same project and use project variables to pass to the child package. You can call or change the value in the child package and the value would be available in the parent package as well.

Answer 3

Here is a way to pass a value from a child package variable to a parent package variable.

Script Task: (in child package)

// Populate collection of variables.
// This will include parent package variables.
Variables vars = null;
Dts.VariableDispenser.GetVariables(ref vars);

// Lock the to and from variables. 
Dts.VariableDispenser.LockForWrite("User::MyParentPackageVar");
Dts.VariableDispenser.LockForRead("User::MyChildPackageVar");

// Apparently need to call GetVariables again after locking them.
// Not sure why - perhaps to get a clean post-lock set of values.
Dts.VariableDispenser.GetVariables(ref vars);
vars["User::MyParentPackageVar"].Value = vars["User::MyChildPackageVar"].Value;

vars.Unlock();

This code was actually from a pre-2012 SSIS Package that I just finished upgrading to SSIS for SQL Server 2012 (in Visual Studio 2012), and converted to project deployment model.

Initially, the execution died (after a lengthy delay) on the variable assignment line. But then I added the "User::" prefix, which apparently was necessary for at least one, but not all of the variables I was assigning this way. The prefix was not necessary in SSIS for SQL Server 2008.

Answer 4

The Simple Solution is

Add The Variables in Master Package (which Needs Back to use in Master)

Assign Values in Child Packages to Master Package Variables (Access no issue in child for Master Package Varibales)

And Once Control came back to Master Used them ...and because they are declared in Master Package you can use their values which were assinged under Child Packages

Answer 5

I have spoken with a couple experts about this there is no NATIVE way of doing this in SSIS. I did see a mention about a way of doing this which is effectively a hack in a script task, but anyone reading this store results from your variables into a table and reference from there. Parameter binding on the execute package task is currently a ONE way street Parent --> Child.