A Shortcut for Parameterizing Settings in SSIS

I’ve written quite a bit about the benefit of externalizing changing values in SSIS packages. Moving static values such as connection strings and file paths to a configurable input makes easier the tasks of testing, changing, and auditing the process in the future. The short and generic story here is: don’t hard code values that can change.

In SQL Server Integration Services, SSIS parameters are the ideal vehicle to manage the externalizing of changeable values. Parameters can exist at either the package or project scope, but the task list to set up parameters remains the same for either:

• Set up the parameter at the desired scope. Specify whether it is required, whether it is sensitive, and optionally set a default value
• Set the property of the expression in the SSIS object to use the runtime value of the parameter

Most of the time these two tasks are performed separately. In fact, in my SSIS introductory classes I always show these two steps independently to make clear the two steps required to set up this design pattern. However, there is a shortcut to both create the parameter and attach an SSIS object setting to said parameter in a single step.

A Shortcut for Parameterizing Settings in SSIS

For those objects in SSIS that support expressions – which includes most of the tasks, components, and connections – there is an easy right-click shortcut to open up the parameterization window. Simply right-click the object to be parameterized and select the Parameterize… option as shown below.

The Parameterize window will appear to allow the parameterization of one of the properties for the selected objects. As shown below, all of the options available when creating a parameter alone are accessible in this UI.

The above window is context aware. The Property drop-down list only displays properties for the selected task that can be parameterized – that is, those properties in which expressions are accessible. In this case, because I clicked on an OleDB connection, only the expressionable properties for that type of connection are shown. Also, the UI above is aware of scope issues. Specifically, it is not possible (by design) to assign a package parameter to a project connection, so the Parameterize UI restricts that activity just as the Expressions UI does when configuring parameter mappings conventionally. As shown below, opening the Parameterize window for a project connection will disable the Package selection for parameter scope.

The examples above show how to configure parameters on connections, but the process to do so for other objects (tasks, components, containers, etc.) would largely be identical. As noted, the Parameterize UI is context aware, so it will present to the developer only those properties available for the selected object.

Conclusion

Parameterizing settings in SSIS is not a difficult task, but is conventionally a two step process. Using the Parameterize shortcut to both setup the parameter and the property mapping will save some time by consolidating both of those operations into one.