SSIS 2012 pass values from child package to parent with project deployment model

16,528

Solution 1

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.

Solution 2

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.

Solution 3

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

Solution 4

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 :)

Share:
16,528
jhowe
Author by

jhowe

Updated on July 18, 2022

Comments

  • jhowe
    jhowe over 1 year

    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.

    enter image description here

    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.

    enter image description here

  • knkarthick24
    knkarthick24 about 8 years
    Very good Solution with the explanation. You scripted it very well.
  • Rick
    Rick almost 8 years
    What a shame :( ... But your post is old already. Does anyone know if there's any news on this?
  • srini
    srini over 7 years
    Thanks you, this answer helped me. However, it was not easy to comprehend in the first read. Thanks anyway.
  • Zorkolot
    Zorkolot about 6 years
    The first Dts.VariableDispenser.GetVariables(ref vars); is not required. It does not have to be written twice.
  • vikrant rana
    vikrant rana almost 5 years
    @Zach Blocker. Is this solution works with 2015 version as well?
  • Zach Blocker
    Zach Blocker almost 5 years
    I don't know - still using 2012 to maintain our packages. Post back what you find out.