Wednesday, February 25, 2009

Passing variable to Child package from Parent package in SSIS

(APPLICABLE ONLY IN SSIS 2005, Use Parent Package Configuration for SSIS 2008)

Very frequent Question which looks so complicated to programmers.

Actually passing a variable value to a child package is very trivial task. We can pass on the value by configuring parent variable in package configuration but there is an easy way of achieve this and the fact lies beneath the fundamental principle of Variable Scope.

If you call a Child package then it is like a container itself and all the variables defined in above hierarchy will be accessible in the Child package.

Let me show this with an example in which I will declare a variable "ParentVar" in my parent package and call a Child package which will access "ParentVar" and display in a msgbox.

1) Parent: Create Parent Package and declare a variable "ParentVar"


2) Child: Create a Child package and use a script task and define readonly variable as ParentVar



3) Child: Now in Script you can use ParentVar like any other variable.eg I am using to display it in a msgbox. I would suggest to create another child package variable and assign Parent package variable value to it and use it in the child package variable through out the package.


4) Child: Whole Child package will look like


5) Parent: Now in parent package call the child package through Execute Package task.
The Parent package will look like


6) Result: Execute Parent package.it will in turn call child package and it will display msgbox


Here was a simple method to use parent package variable in Child package.

6 comments:

Anonymous said...

I don't the variable created in parent..in the child package when I go into the script task...can you please elobrate it more............

Rahul Kumar said...

@Anonymous, It took me some time to understand your question.. anyway thanks for it coz I should have updated this post by saying this only worked till SSIS 2005. For SSIS 2008 please use Parent Package configurations in Child package.

Anonymous said...

thanx for explaining so well for ssis 2005..can you explain same for ssis 2008..
regards

Anonymous said...

i have in oledb 1 to 15 but in the output i wanted 1 as jan, 2 as feb and so on and 13 to 15 i need to get as invalid month name in SSIS

Aakash Sathawane said...

Hi rahul,I am working on SSIS(sql server 2012) ...in that I have many hard-coded values in child packages which i want to replace. I tried using variables but for one hard-coded value, I created one variable each in parent and child package and used execute sql task tool,created parent package variable via package configuration( in parent and child package) for passing the value. All this I have done for one hard-coded value. :(
Is there any easy way which I can use?
Guide me...

janardhana reddy said...

good stuff

Post a Comment