In DTS, changing package properties was close to impossible (the less we talk about DTS, the better). Package configurations introduced in 2005 solved most of the problems. In 2012, SQL Server Integration Services introduces parameters to make this process even easier. Parameters allow you to change package properties without the need for configuration files.

In this example, we will create a variable and change it’s value at run time using parameters.

Let’s enter the Demoland!

In a SSIS package, I created a variable called PromptValue. I assigned the value “I’m Variable” to it.

Next, I added a script task to the package, entered PromptValue as a read only variable, and used the following script.

MsgBox(Dts.Variables(“PromptValue”).Value.ToString)

I see the following message box when I execute the package.

Now, we want to be able to change this value during execution. Here is where we would create a package configuration and add this variable to it, so we can simply change the configuration file without editing the package.

But this time we will use parameters. I added a parameter called ParmPromptValue and assigned “I’m Parameter” as the value. Parameters are of two types: Package and Project. Package parameters can be used within the package. Project parameters can be used by all the packages in a project.

I created a package parameter.

Next, we’ve to pass the value of this parameter to the variable we created earlier. We will use expressions to do that. We know we can use other variables in the expressions builder. In addition to that, we can use parameters. I dragged the package parameter @[$Package::ParmPromptValue] to the expression window.

What should we see when I execute the package? Should it be or should it be ?

Since we used an expression to the variable, the value of the parameter overwrites the value of the variable.

We’ve so far executed the package from Visual Studio. In the below image, I deployed the package to SSIS catalog and assigned a different value to the parameter. More about deployment in the next post.

This is what I see when I run package from SSIS Catalog (Server).

Why in the world would you want to change the value like in this example, you might ask. I agree. This is a fictitious example, but think of connection strings, database names, user name/passwords that you can’t live without changing when moving packages through environments.

I’ve provided an introduction to parameters in this post. Be sure to read the following for more information. Parameters in Denali by Jamie Thomson and this from TechNet Wiki.

Resources

Download the sample package from here.

~Sam.

Follow Sam on Twitter – @SamuelVanga