How can I dynamically set the location of an Execute Package Task in SSIS

Question

I'm trying to set up a 'master' SSIS Package in SQL Server 2008 to run other 'child' packages. When developing the 'child' packages we have all the packages sitting on disk so we can easily debug them, so we use file connectors during development and can monitor the progress nicely.

When we deploy, we deploy the child packages to SSIS on SQL Server and then go through and change all the Execute Package Task's to use a location value of 'SQL Server' and set the PackageName. Once done, we deploy the 'master'.

What I'd like to do is use an Expression on the Execute Package Task to set the connection properties so we can configure this dependent on the environment. We have already setup a SQL Server configuration database using a view which checks the host name of the query and returns different values dependent on the query.

Answer 1

You have options. You're in the right frame of mind using expressions, but you might benefit from using configurations as well.

To use expressions, you would need to use a Script Task or Execute SQL Task to return back the list of files you want to work through.

You would either have to assign each returned value to it's own variable that is passed into the expression, or use a FOR EACH loop and work through a list, assigning the location of the child package each time.

The other option is to use configurations. My preference is to use a configuration table inside SSIS. If you have the same list of packages in each environment, you could either pass in the root directory and have an expression use that: @[User::RootPackagePath] + "\PackageName.dtsx"

Or, you could simply have one record for each child package in the configuration table and that would be passed into the package.

Edit based on comments:

I was successfully able to configure a package to change via configurations to call a package from the file system then SQL.

I only needed to pass the Connection and PackageName for each. With SQL, it want a name from the connection manager (.\SQL2008R2 in my case) and the package name (\Package1). For the file system, PackageName is blanked out and the connection is a FileConnection in the connection manager.

You will have to keep both in the package, but you switch between the two.