Execute a package from another package with variable – SSIS
It is just executing a package stored in SQL Server or File system from a root package. Perhaps, you can say parent – Child package.
Execute Package Task
This task helps SSIS developer to design a complex package into multiple chunks of packages. It gives solution a big problem with multiple small tasks.
There are other interesting things you can learn in MSDN. http://msdn.microsoft.com/en-us/library/ms137609.aspx
Purpose of this article
Create a package and calling another package is very simple by placing Execute package task and point to the SSIS package in SQL Server or File system.
To make this article more interesting, I am going to explain “How to pass a value from the parent package to child package.
Step 1: Create a package Name Parent.dtsx and create another package name child.dtsx
Step 2: Create a String variable name VarParent and assign “Hello Child” value in the parent package and assign variable scope to package level. I assume you know how to create package variable with scope. No worries learn now http://msdn.microsoft.com/en-us/library/ms141670.aspx
Packages and Variable definition
Step 3: Add Execute Package Task in to parent package and connect to child package
Edit the Task and go to Package tab
- Assign File System value to the Location property
- Assign Child.dtsx package path to the connection property. It will create a connection in the package connection manager.
Step 3: click OK and open child package and add a script task in to the package.
Step 4: Add a String variable to the child package with package scope
Step 5: Edit the Script task to show the value in a message box while execution.
Step 6: Assign parent package variable value to child package variable
- Open Package configuration wizard
- Enable package configuration and Click Add button
- Click next and go to package configuration type and select Configuration Type “parent package variable” and add the parent variable manually into parent variable textbox. Click next
- Select the target property to Child package variable value. Click Next
- Click Finish button to complete the package configuration with default configuration name. You can also give your any name to package configuration.
Step 7: Click Close button.
Step 8: Execute the Parent package
Note: Package variables are case sensitive.
Thanks for reading. Please rate this article.