posted 12/22/2009 by stirone - Views: [4855]
Someone was asking how they could dynamically execute a given package dynamically, without hardcoding the package name into the Execute Package Task. Well, I believe this is what they were asking anyway, and it is a good enough excuse to talk about Expressions in SSIS again.
You probably already know about Expressions, but let's just add one more example to the list, dynamically executing a given child package at run time. This is easily accomplished by first creating a variable to hold the name of the package you want to execute. You may also want to create a variable to hold the name of the connection to the server holding the child package. For example:
Now, in the Execute Package Task, set it up normally, giving it a known Connection and Package Name. You won't be able to avoid having to do this, but it won't matter, because at run time the package will evaluate the expressions that we are about to create for these properties, and use those values instead.
To create the Expressions, click on the Expressions item in the Execute Package Task Editor, and click the ellipsis button to get into the expression editor. Select PackageName from the Property dropdown list, and the ellipsis button again. From there, expand the Variables tree and find your User::childPackageName variable, and drag it to the Expression box.
Do the same for the child Package Connection if you need to. That's all there is to it. When you run the main package, whatever name you have in the childPackageName variable will be the package that will be executed by the Execute Package Task (granted that it exists on that connection, of course).