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.
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.