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.

Implementation

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

  1. Assign File System value to the Location property
  2. 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.

6 thoughts on “Execute a package from another package with variable – SSIS

  1. Can you still run the ChildPkg without the parent variable?
    What I mean is, is there a way to bypass that variable IF not found in ParentPkg and use instead a variable within the ChildPkg?

    For example, I have a ConnectionString as a variable within the ParentPkg, the ChildPkg uses that variable, but for ever reason I need to run the ChildPkg individually, the pkg will fail because it cannot find the ConnectionString value.

    Thanks.

Leave a Reply