A few months ago I did a post on how to pass the value of a parent package to a child package. That solution consisted on a peace of .Net coding in a Script Task. But there is an other solution for those who don't like .Net coding.
Solution
This solution consists of using the package configuration.
1) Create parent variable
Create a variable in your parent package. Right click in the Control Flow if the variables are not visible. I used a string variable named FilePath in this test case to store some filepath.
 |
| Create new variable, think about the scope. |
2) Create Execute Package Task
Drag an Execute Package Task in your Control Flow and configure it to start your child package.
 |
| Execute Package Task |
3) Create child variable
Goto to your child package and create a variable with the same name as your parent package variable.
 |
| Same name! |
4) Add configurations
In the SSIS menu choose Package Configurations.
 |
| Package Configurations |
5) Configuration Type
Enable package configuration(1) and add a configuration. Choose Parent package variable(2) and type the name of the variable(3).
 |
| Parent package variable |
6) Select Target Property
After you typed the name of the variable, hit the next button and select the value property of the child package variable. After that hit Next, Finish and OK to complete the configuration.
 |
| Value property of the child package variable |
7) Testing
To test the package I added a simple Script Task (sorry for .net coding) with a messagebox to show the value of the parent package.
 |
| Little bit of coding, but just for the show. |
The SSIS solution can be downloaded here.
Is there any difference between the two methods?
ReplyDeleteWhat is the limitation for both of them beside the child can't run on its own?
@dczepyrus: I haven't detect differences, besides the one you mentioned. The result of both methods is the same. You can test it yourself with the solution you can download.
ReplyDeleteI used two jobs(J1 and J2) with SQL server configuration types. Both jobs runs independently. But J2 is the subset of J1 (in terms of operations). So i made J2 as Child and J1 as parent. When i try J2 has multiple configurations types(SQL Server and Parent Configs both), it just don't work. J2 refers to SQL server config type only. It's not overriden by Parent Configs after setting up Paretn config type on the top though. J2 has to run independently too and when J1 runs J2 should run with parent configurations.
ReplyDeleteIn short, the question is how to override one configuration type with other. Sorting of config types in package configurations organizer don't work. Is this is a bug in SSIS 2008 ?
Parent package variable configuration only work if you call the child package from the parent package. And beware of the order of events in a package when you're using Parent Package Variable Configuration. Order is different to other configurations: More info.
DeleteAlso see the SSIS MSDN forum for general SSIS questions. You will get a lot more/faster/better answers.