SSIS 2016 – Passing Variable from Child Package to Parent Package

ssisssis-2016

I have an SSIS project consisting of many packages, including a Main_Flow package which executes the other packages in the desired order.

The first package to be called from Main_Flow is Extract_Archive with a ForEach container that looks for zipped files. If one or more zip file exists, it extracts them in a location and marks a variable ArchiveFileFound to TRUE.

Now back in the Main-Flow package, I would like the next step to be executed ONLY if the ArchiveFileFound from the Extract_Archive is set to TRUE. However that variable only exists in Extract_Archive. I've been looking for information on how to pass variables from one SSIS package to another but I cannot find a good practical and recommended example. Any thoughts?

Best Answer

This can be done using a Script Task in the child package to update a variable from the parent package based off the value of a variable in the child package, assuming that none of the variables that are accessed have the same name in both the parent and child packages. While the variables from the parent package won't be displayed in the ReadWriteVariables field on the Script Task, if you enter them here then they can be accessed. Updating the variables from the parent package would follow the same syntax as doing so for variables in the same package, and the C# example details this.

Dts.Variables["User::ParentVariable"].Value = Dts.Variables["User::ChildVariable"].Value;

Related Solutions

Sql-server – Passing values to variables defined in SSIS package at run time

You can have the values specified in a .dtsconfig file, or you can set them using the /SET option of dtexec.exec

SQL Server 2008 – SSIS Package Configurations Ignored

You must take into account that when running by BIDS the package will take first the variable value from the config file, and only if the config file doesn't exist, it will throw a warning and the value will be taken from the package.

Now, the situation in command line is a bit different. You can have the following situations:

run the package in cmd line without any config file chosen:
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx"
```
- if the original config file (let's name it Prod) doesn't exist in the same path defined in the metadata of the package, values from inside the package are used and you'll just receive a warning that config file is missing;
- if the original config file exists and is valid, then values from the config file will be used (inner values will be bypassed);
run the package in cmd line without any config file chosen, but with variable set in the call:
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /SET \Package.Variables[checkMe];"outside the package in cmd line"
```
- if the original config file doesn't exist, then the value is taken from the /SET package call;
- if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);
run the package in cmd line with a new config file (let's say DEV instead Prod):
```
dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig"
```
- if the new config (Dev) file exists, and old (Prod) doesn't, then values from it are used;
- if both the Dev and Prod config file exist, then only values from Prod is used (DEV is bypassed even if specified in command line call);

run the package in cmd line with a new config file and a SET statement in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig" /SET \Package.Variables[checkMe];"outside the package in cmd line - DEV config"

if both config files exist, Prod will be used, all others ignored, even the SET;
if no config file exists, SET value will be used;

So, in short, if you want to use a new config file you'll have to rename/move the old one and call the package with /configFile. If that's not enough and want to override even new config file, then use the /SET variable. Or you can bypass any config file and just use /SET statements in the batch call.

Hopefully that will shed some light in your possibilities.

Best Answer

Related Solutions

Sql-server – Passing values to variables defined in SSIS package at run time

SQL Server 2008 – SSIS Package Configurations Ignored

Related Question