Change Package Properties Using Parameters–SSIS 2012

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.

SSIS Parameters 2012

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.

SSIS show messgae box

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.

ssis package parameters

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.

image

What should we see when I execute the package? Should it be [I’m Variable] or should it be [I’m Parameter]?

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

image

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.

image

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

image

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

About these ads

5 thoughts on “Change Package Properties Using Parameters–SSIS 2012

  1. Pingback: How to execute a package from another package? « Sam Vanga's Blog

  2. Hi,

    I followed the steps you described above. When I ran the package the messagebox showed “I’m Variable”. Not sure what did I do wrong. Any suggestions?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s