How to change properties of package objects at runtime?
DTExec and SSIS Packages does not support the concept of parameters. It is possible to change object properties values at run time in two way: through
Package Configurations or through the standard /Set commandline option.
In the latter case, as you may have noticed, obtaining the path of the property you want to set is not very convenient. As
BOL says:
"You can determine the value of propertyPath by running the Package Configuration Wizard. The paths for items that you select are displayed on the final Completing the Wizard page, and can be copied and pasted. If you have used the wizard only for this purpose, you can cancel the wizard after you copy the paths."
The needing for Package Parameters
Usually I make my package configurable using both the options. I use the Package Configurations to store all the connections that the package uses. Connection does not change so often, so I just have one Package Configuration that allows me to point to the correct XML Configuration file when running packages in the Development environment, in the Test evironment and on the Production server.
Of course there are some configuration that needs to change much more often. For example think of a package that load data on month-by-month basis. Surely such package will have a variable, let's call it
User::MonthYear that hold the year and the month to load.
Using the /Set option it's possible to change that value at run time:
/Set:"\Package.Variables[User::MonthYear].Properties[Value];200907"
So basically that variable is really being used as a package parameter, just to be able to change some values at runtime without having to open and edit the package or to use Package Configurations.
My personal best pratice, in order to differentiate between variables used internally by the package and the ones used to make the package easy to configure, is to name the variables used as paramters with a
param_ prefix. Another option I use, depending on the customer and the project, is to put those variables in the
param namespace.
Package Parameter support in DTLoggedExec
To make life easier DTLoggedExec is aware of the situation described above and recognizes those variables allowing you to simply specify your parameter name, which is the variable name without the prefix nor the namespace, and that's all!
/Param:"MonthYear;200907"
Create a Package Parameter
Creating a Package Parameter is very easy. As said in previous paragraphs, a Package Parameter is nothing more than a variable with a "special" condition. The condition is one of the following:
- Variable name is prefixed with param_
- Variable lives in the Param namespace
Display Package Parameters supported by a Package
Once your package as been defined using such special-named variables that act as Package Parameters, DTLoggedExec is able to analyze your package and tell you which parameters it has. All you have to do is specify the option
/ShowParams (or
/SP) in the command line. Eg:
DTLoggedExec /FILE:"./Test Package 10.dtsx" /SP
DTLoggedExec
won't execute your package, it will simply analyze it, show this result:
As you can see, beside Package Parameter name you also have the Default value (the value used by the package if you don't supply any value for that parameter) and the Description which is simply taken from thew Variable's Description Property.
Usage
To set value for an available Package Parameter you have to use the
/Param option. Here you have to specify the Package Parameter name (which is simply the associated variable name without the
param_ prefix nor the namespace specification) and then the parameter value separated by a semicolon:
DTLoggedExec /FILE:"./Test Package 10.dtsx" /Param:"CustomerId;10"
And this is the result:
As you can see Package Parameters are applied
before the
/Set option, so be aware not to overwrite values set through Package Parameters when also using the
/Set option.