How to: Use Values of Parent Variables in Child Packages

  • Article

New: 5 December 2005

This procedure describes how to create a package configuration that uses the parent variable configuration type to enable a child package that is run from a parent package to access a variable in the parent.

It is not necessary to create the variable in the parent package before you create the package configuration in the child package. You can add the variable to the parent package at any time, but you must use the exact name of the parent variable in the package configuration. However, before you can create a parent variable configuration, there must be an existing variable in the child package that the configuration can update. For more information about adding and configuring variables, see How to: Add a Variable to a Package Using the Variables Window.

The scope of the variable in the parent package that is used in a parent variable configuration can be set to the Execute Package task, to the container that has the task, or to the package. If multiple variables with the same name are defined in a package, the variable that is closest in scope to the Execute Package task is used. The closest scope to the Execute Package task is the task itself.

To add a variable to a parent package

  1. In Business Intelligence Development Studio, open the Integration Services project that contains the package to which you want to add a variable to pass to a child package.

  2. In Solution Explorer, double-click the package to open it.

  3. In SSIS Designer, to define the scope of the variable, do one of the following:

    • To set the scope to the package, click anywhere on the design surface of the Control Flow tab.
    • To set the scope to a parent container of the Execute Package task, click the container.
    • To set the scope to a parent container of the Execute Package task, click the task.

  4. Add and configure a variable.

    Note

    Select a data type that is compatible with the data that the variable will store.

  5. To save the updated package, click Save Selected Items on the File menu.

To add a variable to a child package

  1. In Business Intelligence Development Studio, open the Integration Services project that contains the package to which you want to add a parent variable configuration.

  2. In Solution Explorer, double-click the package to open it.

  3. In SSIS Designer, to set the scope to the package, click anywhere on the design surface of the Control Flow tab.

  4. Add and configure a variable.

    Note

    Select a data type that is compatible with the data that the variable will store.

  5. To save the updated package, click Save Selected Items on the File menu.

To add a parent package configuration to a child package

  1. If it is not already open, open the child package in Business Intelligence Development Studio.

  2. Click anywhere on the design surface of the Control Flow tab.

  3. On the SSIS menu, click Package Configurations.

  4. In the Package Configuration Organizer dialog box, select Enable package configuration, and then click Add.

  5. On the welcome page of the Package Configuration Wizard, click Next.

  6. On the Select Configuration Type page, in the Configuration type list, select Parent package variable and do one of the following:

    • Select Specify configuration settings directly, and then in the Parent variable box, provide the name of the variable in the parent package to use in the configuration.

      Important

      Variable names are case sensitive.

    • Select or Configuration location is stored in an environment variable, and then in the Environment variable list, select the environment variable that contains the name of the variable.

  7. Click Next.

  8. On the Select Target Property page, expand the Variable node, and expand the Properties node of the variable to configure, and then click the property to be set by the configuration.

  9. Click Next.

  10. On the Completing the Wizard page, optionally, modify the default name of the configuration and review the configuration information.

  11. Click Finish to complete the wizard and return to the Package Configuration Organizer dialog box.

  12. In the Package Configuration Organizer dialog box, the Configuration box lists the new configuration.

  13. Click Close.

See Also

Tasks

Creating Package Configurations
How to: Create a Package Configuration
Using Variables in Packages

Concepts

Package Configurations
Integration Services Variables
Variables How-to Topics (SSIS)

Help and Information

Getting SQL Server 2005 Assistance