SSIS Expressions

Expressions in SSIS are great. They allow you to create dynamic values for all sorts of stuff like variables, connection strings, properties for almost anything, etc. One huge issue that tends to trip up a lot of folks, especially those new to SSIS, is the evaluation of those expressions when using variables.

The Issue

You create an SSIS variable with an expression but at runtime the expression is not evaluating as expected. Instead the expression value is using the default/static value of the variable.

Quick Answer

Make sure property for ‘Evaluate as an Expression” on the variable is set to True. Without it being set to true, the variable will evaluate to the hard set value.

Explanation

Even if you create an expression on the variable through the expression editor, and even if you test the expression and it evaluates correctly in the editor, the package will not use that expression unless you explicitly set the property on that variable to evaluate as an expression. When the property is set to false, the package will evaluate the hard set value and not the expression! In order to clearly show this in action, I’ve created a quick video below showing this behavior in action.

Example:

To demonstrate this behavior I created a simple SSIS package that has a single variable named strMessage. The value I set for it is Manual text. Go to the properties for the variable, find the properties for expressions and click the ellipses button to open the Expression Builder. In the Expression window copy/paste this expression:

“This is an expression with a date: ” + (DT_STR, 30, 1252) GETDATE()

You can hit the Evaluate Expression button to verify the code is evaluating correctly. Click OK to close the Expression Builder.

In the Control Flow I’ve created a Script Task that creates a message box that displays the value of the variable. Here is the code (VB) inside the script task’s main section of code:

Public Sub Main()

‘

MsgBox(Dts.Variables(“strMessage”).value)

‘

Dts.TaskResult = ScriptResults.Success

End Sub

This code simply populates a message box with the value from strMessage variable.

Don’t forget to supply the variable name in the ReadOnlyVariables property of the script, otherwise the script task won’t be able to read the variable from the package.

If you execute the package you’ll get a pop up box that should show you this:

Notice how the value of the message is pulling from the static value of the variable and not the expression? Now stop the package from running (hit the Stop button or press Shift+F5).

Go back to the properties for your variable and look for a property called EvaluateAsExpression. Change the value of that property to True.

Now run the package again, this time you should see: