CODEWITHSUNDEEP
ssis
Metaphor
Metaphor

Reputation: 6405

SSIS Script Task: How do I set a variable in one package and use it in another

I need to use a variable I get in a Script Task in one package in a Script Task in another package. How can I make a variable with a scope that spans packages? Are there Project variables?

Upvotes: 1

Views: 2007

Answers (2)

Anoop Verma
Anoop Verma

Reputation: 1505

In SSIS 2005 and 2008: Declare a variable - say p as int - at the package level Call your child package from the parent package. Now, if you have a script task in the Child package, you can access the variable p like this: 1. Pass ReadOnly variable p in the Script Task Editor of Child package 2. To access the parent variable: Dts.Variables["p"].Value; Notice, that I have not use "User::p" in any of the above two steps. I find this method straight-forward. Make sure you do not declare a variable p at the child package level.

So, how does this method work? Think of the basic concept of the scope of a variable. The script task will keep "going up" to find the variable p. Going up means - it will first try to find it at task level, then container level, then package level, then finally at parent package level. (This is a simple explanation - technically each of these levels are containers.)

In SSIS 2012, you can also use parameters to pass the variable in ReadOnly mode. The method described above can also be used in SSIS 2012 with added advantage of being able to overwrite the value of the parent variable.

Upvotes: 1

billinkc
billinkc

Reputation: 61211

Prior to SQL Server Integration Services 2012, the only way to share a value between packages was to use Parent/Child configuration. You could actually share a value between them without using configurations but it was janky as all get out.

If you have no need of bi-directional communication, then you could have package A (one that computes the value in script task) start package B and use the /SET properties to assign a value to the variable

dtexec /file PackageB.dtsx /Set \Package.Variables[User::SharedVariable].Properties[Value];\"I was computed\"

In a SQL Server 2012 project deployment model, the Configuration concept has been replaced with Parameters. This is a bit more straight forward as you define Parameters and specify whether they are required. The Execute Package Task provides a nice mapping mechanism between local variables and expected Parameters.

Upvotes: 2

Related Questions