SSIS 2012 Favorites: Parameters

SQL Server 2012 has made some great enhancements to Integration Services. I wanted to write posts on some of my favorite features of this new version. The intended audience is for people evaluating the software, thinking about upgrading, or wanting to learn more about SSIS 2012.

Intro to Parameters

In SSIS pre-2012, SSIS packages could be controlled externally at runtime via configurations with either XML, a SQL Server table, or an Environment Variable. You would do this typically for portability and control, a common example being deployment between development and production servers. There are other uses for configurations that come in handy:

• Setting precedence over what work gets done
• Dynamically changing SQL statements or .NET code
• Securing credentials

Now with SSIS 2012, Microsoft gives ETL developers parameters. Parameters are variables truly meant to be controlled external to the package. Why? Well because their awesome, that's why! Let me show you how simple, yet powerful, parameters can be.

Example: Parameterize a Connection Manager

One common use of SSIS package control via configurations was to enter server and database names depending on whether you were deploying packages to development or production servers. The difference usually being the names of the server and the source & target databases. So let's see how to get this done using the new Parameterization means in SSIS 2012.

After creating a connection manager, you simply right-click on them and select Parameterize in the menu. 

The dialog prompts you for a Property of the Connection Manager to Parameterize. You can create a new parameter and specify a name, description, and value all within this dialog. The parameter description, believe it or not, is useful when configuring the package on the server. In my example above, I chose to make the InitialCatalog property dynamic to point to the correct name of the database whether I was running the package for development or production.

Note the other options for Parameters at the bottom of the dialog. Scope determines whether this parameter will be available to the entire SSIS project or just this specific package. Other options include saving sensitive information like passwords and making this parameter required. Man, parameters, where have you been all my life!

So once you hit OK, there's a couple places to look for the implemented changes by the dialog wizard. 

1. Go to the Properties window with the Parameterized Connection selected and look under Expressions, you'll see the SSIS expression for the Parameter in the InitialCatalog property for this Connection Manager.
2. Above the Package designer is a new tab called 'Parameters'. Click on it and you will see a new row where the new Package Parameter has been configured.

The Parameter Encore: SSISDB and SQL Server Agent

Lastly, you can configure runtime values of a parameter in one of two places: the SSISDB Catalog or the SQL Server Agent. The new SSISDB Catalog in 2012 is now the repository for all package deployments, so it makes sense that we specify parameter values here, especially if segregating dev and prod packages in a single catalog. Otherwise, you can just set a parameter in a SQL Job Step.

Setting parameters in the SSISDB Catalog is easy. Navigate to the project/folder/package in the Integration Services Catalog node of SSMS, right-click and select Configure. You get the dialog below where you can click the elipses button to leave the parameter default value or set it to something custom. Note the description of the parameter is displayed, which is useful for DBAs or BI developers not intimately familiar with the details of the package. I like this feature a lot.

Setting parameters in a SQL Job under the SQL Server Agent is easy after getting to a Job Step. So I'll skip the steps about creating a job and just go straight to the Job Step, where you'll see a Configuration tab. Click on that and you'll see where you can set the parameter in the job.

So there you have it folks. Parameters. What Configurations should have always been.

Please note that the Parameterize menu item is only available for Connection managers. To create package parameters and specify them in properties to be externally configurable, remember to follow these steps:

1. Create a project parameter or a package parameter first. Project parameters are available by clicking on them from Solution Explorer. For package parameters, go to the Parameters tab above the package designer.
2. Specify the parameter as an SSIS Expression for a property, a variable (like dynamic SQL), or in a precedence constraint. 

Look for more SSIS 2012 Favorite posts to come.

4PMX3VRH2FKX