Dynamically changing a SQL Server Integration Services (SSIS) package at runtime is something many SSIS designers will find themselves doing. There are a number of methods of doing this and I've covered the main ones already:
Configurations and command-line parameters provide very similar functionality so the question is, when should each of them be used?
Configurations would typically be used when the values that you are passing into your package do not change very often. The obvious example would be where we are moving our packages between different environments and for each environment we need to specify different server names for our data-flow sources and destinations.
Command-line parameters are ideal for situations where the value passed into the package can change often, perhaps on an ad-hoc basis. For this reason it is not possible to know prior to executing the package what value is required to be passed to the package - hence a configuration will not suffice (I have heard talk of another application editing the configuration file before executing a package but this would definately come under my own category of "Worst Practises"). Command-line parameters would be ideal for when an SSIS package is called on an ad-hoc basis by another process. For example:
- An ISV wants to incorporate SSIS functionality into their product but certain functions in that package can only be carried out by certain users of the product. In this situation the product could pass in the current user's ID which the package can then use to check an external table to see if that user has permission to use the functionality provided by the package.
- File paths for flatfile connection managers change on an ad-hoc basis and we want to pass in the path when the package is executed.
Make sure that you understand where command-line parameters & configurations should and should not be used. They're mightily powerful tools to have at our disposal.
-Jamie