Cheryl on the Web

Disclaimer: If you are not familiar with the SQL Server 2005 equivalent of SQL Server 2000 packages - SSIS packages - then you need not read further.

For the rest of you: beware of the limitations you will face when using the Execute Integration Services Package task in SSIS workflow for SQL Server 2005. Under other circumstances, when running such a package as a job or manually, you can pass in configuration settings using XML configuration files or using command line parameters. Unfortunately, when running a package as a nested (”child”) package using this SSIS package task, you can’t pass in any configuration options - no XML configuration files, no command line parameters, nada. So if you’ve developed the child package in a development or test environment and wish to use it in production, you can’t; the configuration saved into the child package at run-time will be used when it is executed as a child package.

However, there is a workaround. Here we go:

1. In the parent package, create a global variable for each connection (or property) for which the child package requires configuration information. For instance, maybe you create a variable “Child_DBConn” to hold the connection string of a database.
2. In the parent package, add a script task or use XML package configurations to assign values to the variables created in step 1. For instance, add a script task in the Parent package that assigns the variable the value of a data source connection string (e.g. Dts.Variables(”Child_DBConn”).Value = Dts.Connections(”MyDB”).ConnectionString).
3. In the child package, create variables to correspond to the ones you created in the Parent package (e.g. “Parent_DBConn” to correspond to “Child_DBConn”). Important: assign these variables valid default values, otherwise you will not be able to design the package without lots of errors after you complete the next step. (Tip: Visual Studio will tell you the current connection string of your database connection in the parent package if you double-click on it to open it.)
4. In the child package, use the Expressions property of the connections (or other items) you need to configure, and assign their values based on the local variables. For instance, use Expressions on a data source in the child package to assign the ConnectionString based on the local variable “Parent_DBConn”. At design time, this won’t result in a change to the connection, assuming you have assigned the variable the design-time connection string.
5. In the child package, open the package Configurations. Add a new configuration, and choose as the type Variable in Parent package (instead of the default XML file). Enter the variable name you added to the parent package (in our example “Child_DBConn”, case-sensitive), and in the next step assign this to the variable of the child package (”Parent_DBConn”).

That does it! All of this means that, at run-time, the child package data connection will take on the properties of the local variable connection string, which takes its value from the parent package variable, which is assigned the value of the parent package connection string in a script task, which will of course use the runtime configuration on the parent package data source. That means, you could use an XML config file on the Parent package that then influences in this way the child package. If the child package is run without a parent package, it will use the values saved at design time since the package configuration based on parent package variable will fail.