Hopefully by now you’ve read the introductory post for the 31 Days of SSIS series that I am writing.  Today we are going to look at variables in SSIS packages. 

This post assumes that you already know a bit about variables in SSIS packages.  If there is a groundswell of requests, I can do a post on variables themselves.  So ask if you want to see it.

Time to begin…

Variable Expressions

Back in the DTS days, in order to change the value of a variable we need to write VBScripts.  These scripts were at best irritating to write and were wrought with opportunities for misspellings and unrunnable code.

Enter stage right with SSIS and its variables, which can be evaluated as expressions.  This was a vast improvement over DTS and all of the VBScript that we had in the past was chucked. 

But how exactly do we evaluate variables as expressions.  First, what does it mean to do this?  When a variable is evaluated as an expression, the value for the variable is calculated at run-time based on SSIS package properties, system properties, and other variables to determine a value for the variable.

From here though, let’s see how this works with our own variable.  To start create a package and open up the Variables window.  In this window create a new variable named SimpleVariable.

Once the variable is created bring up the Properties window for the variable.  There will be a number of things to choose from in the properties.  The item we are concerned with is EvaluateAsExpression.  Follow the red arrow below if cannot find the property.  Change this value to True.

When you change the EvaluateAsExpression property to True, an ellipse will be enabled (at the red arrow below) that will allow you to open the expression builder for the variable.

Click on the ellipse and the Expression Builder window will appear.  This is pictured below.  There are a couple areas to pay attention to when building expressions.  First, the area in green below contains all of the system and package variables that are available.  If you are unsure on the name of a variable just browse through this list to retrieve one.  Next, in the yellow area, has a list of all of the operations that are possible in expressions.  These can be a bit tricky to get used to at first and I’ll dedicate a later post to some of their nuances.

For our expression at the moment, type in “1 + 2”.  This is written in the Expression box above.  Selecting the Evaluate Expression button will return the result of 3.

This was a simple explanation of how to build an expression for a variable.  Now onto something much more useful.

The One Variable

Yesterday’s post focused on the configuration of the SSIS environment form a folder perspective.  The reason for that post was because I wanted to talk today about how I will often use one variable in a package as a fulcrum for setting many other variables and configuring other portions of the SSIS package.

In the Folder Structure post, I talked about the WorkFiles folder.  Often I include a variable that has this value which I use to set the values for files and folders that the package must interact with.  The rest of this post will demonstrate this practice.

In an SSIS package, create the following variables; WorkFolder, ArchiveFolder, FileName, LogFolder, and ResponseFile.  Set the value for the WorkFolder variable to “c:\temp”.  At this point your Variables window should look like this:

Now change the EvaluateAsExpression property for ArchiveFolder, FileName, LogFolder, and ResponseFile like we did in the previous example.  What we want to do now is create expressions for these variables based on the value in WorkFolder.  Type in the following expressions for each of the variables:

• ArchiveFolder: @[User::WorkFolder] + "\\Archive\\"
• FileName: @[User::WorkFolder] + "\\ImportFile.txt"
• LogFolder: c:\temp\Log\
• ResponseFile: c:\temp\Response\

Once these are all set, the values for each of the variables should change to the following:

As you can see, the WorkFolder variable now controls the values for each of the other variables.  By just changing the value of WorkFolder all of the locations for all of the files can be changed. 

Variable Wrap-Up

Using a variable or two to control the configuration of your SSIS packages can is relatively simple to do.  In the demo above, the one variable reduced the amount of configuration points needed for the package.  Instead of setting all of the values individually, they are set by the one variable. 

This can help reduce configuration changes when you are moving your SSIS packages from environment to environment.  It can also reduce the amount of work required when troubleshooting a package since only a few variable will vary between environments.

Now that I’ve laid out some ground work, be prepared for a few packages in the next few days that I’ve used to solve business problems.

References

Getting Started with Variables in SSIS

SSIS Package

OneVariable

Related posts:

1. 31 Days of SSIS – Folder Structures (4/31)
2. 31 Days of SSIS – Using Breakpoints (2/31)
3. 31 Days of SSIS – Raw Files Are Awesome (1/31)