SSIS Parameters

SSIS parameters help to ease the process of passing runtime values to SSIS packages. For values that can change over time, using parameters in SSIS is an ideal means of externalizing those runtime values.

In this post, I’ll demonstrate how to get started using SSIS parameters.

What Problem Are We Solving?

To understand the need for ETL parameters, let’s first review the alternative to using them. Let’s say you create an SSIS package that connects to an SFTP server, downloads a set of files, and then ingests those files into a database. If you simply code your ETL logic to use the runtime details as they are now, chances are that the packages will run just fine.

However, it’s very likely that some of the runtime information will eventually change. Among those values that will need to be periodically updated are these:

• Database connection strings
• FTP/SFTP file locations
• User name/password credentials
• API keys
• Local file system paths

If you have hard-coded those values into your package, you’ll have to edit your source code every time such a change is needed. This likely also means another round of testing and validation, adding to the work required to make a trivial configuration change.

A better and more maintainable design pattern is to decouple the source code from the runtime values that may change over time. Things like server addresses, folder locations, and particularly account credentials are necessary but don’t change the fundamental logic of the SSIS package. Therefore, separating values into separate configuration structures allows you to update those values without modifying the source code.

In prior versions of SSIS, this was handled through SSIS configurations. These configurations allowed you to store runtime values in a file, database table, or even an machine environment variable. While they were flexible, these legacy SSIS configurations presented a few challenges:

• There was no way to bind a set of configuration values to a package
• There was no option to make a configuration required for a particular value
• Configurations were set up at the package level, making it tedious to implement on projects containing many SSIS packages
• Obfuscating sensitive values such as passwords and API keys could not be done natively, which resulted in those values being stored in plain text

This was even more complicated when passing a runtime value from a parent package to a child package, where there was no way to easily view or manage the runtime values inherited by the child package from the parent package.

When SSIS parameters were first introduced starting with SQL Server 2012, many of these challenges of legacy SSIS configurations were addressed.

SSIS Parameters

SSIS parameters are placeholders for runtime values. Think of parameters in SSIS as a special type of variable which can be bound to SSIS object properties. The window for Parameters shows that these look a little like native SSIS variables.

As you can see, each parameter has a name, data type, and a default value, similar to an SSIS variable. You also have a couple of additional settings on each:

• Sensitive (true/false): Indicates whether this parameter contains a sensitive value, such as a password or private encryption key. The default value is false. Make sure you set this value to True for any secret value to prevent it from being stored or logged in plain text.
• Required (true/false): Indicates whether a value must be provided at runtime. The default value is false. If you set this to true, the specified parameter must have a value provided at runtime, even if the parameter has a default value.
• Description (text): This optional field allows you to add a description. I recommend you use this to fully describe the purpose of this parameter.

Project and Package Parameters

Parameters can be created at the project level or the package level. There is no functional difference between project and package parameters; they only differ in scope, in that project parameters are visible to any package in the project, and package parameters are visible only within that package.

Project parameters are accessible by opening the Project.params file in the Solution Explorer, shown below.

Package parameters can be found in the Parameters tab on each package.

Using SSIS Parameters in Package Execution

Once created, parameters can be used during package execution in much the same way as native SSIS variables. Parameters (both package scoped and project scoped) are available in the Expression Builder window. The syntax used to reference a parameter in an expression is a bit different, as shown below.

In addition to the Expression Editor window, there is a shortcut to allow you easily add a parameter to some SSIS objects. You can right-click most any connection manager or control flow task and choose the Parameterize option to bring up a window that will create a new parameter and attach it to a specified property for that object.

As shown below, you can choose to create a new parameter, set the name of the new parameter, and specify the property to which that parameter will be bound, all in one easy step.

Passing SSIS Parameter Values at Runtime

Once the parameters are created and the project has been deployed to the SSIS catalog, you can now start the package execution and specify parameter values to be used. Remember that setting a parameter value is for just a single execution only; the parameter value does not persist beyond that single execution.

If you execute the package using the UI in SQL Server Management Studio, that Execute Package window has a tab named Parameters that allows you to specify values for each parameter. As shown below, the project parameters have a different icon than the package parameters to easily differentiate, and the parameters that are marked as Required show an exclamation point beside them until you specify a value. You can click each ellipsis on the right of the Value box to enter the parameter value.

If you create a SQL Server Agent job to execute the package, the UI will look similar to the above. When you create a SQL Server Agent job and specify parameter values, those values will be used for every execution of that job.

If you need to use T-SQL to execute a package containing a parameter, you’ll need to use the [catalog].[set_execution_parameter_value] stored procedure as shown below.

You can also use an SSIS catalog environment to pass in multiple parameters at once, but I’ll save that topic for a future blog post.

Final Thoughts

Here are a few things to keep in mind about parameters:

• Parameters are read-only during the execution of a package. Unlike package variable, a parameter value cannot be modified during the execution of the package.
• When you execute a package in the SSIS catalog, parameter values are logged to the [internal].[execution_parameter_values] table. Don’t forget to mark any secrets with the Sensitive setting, otherwise the parameter values will be logged to that table in plain text.
• I recommend using a consistent naming convention for your package and project parameters. I use a “p” preface for my package parameters and a “pp” preface for the project parameters.