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.
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.
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.
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.
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()
Dts.TaskResult = ScriptResults.Success
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:
11 Replies to “Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly”
When the value of the variable is set by an expression, the ValueType property is automatically updated to a data type that is compatible with the evaluation result of the expression. For example, if the Value property contains 0 and ValueType property contains Int32, and you then set Expression to GETDATE(), the Value property now contains the current date and time and ValueType is set to DateTime. For more information, see How to: Set the Properties of a Variable at http://msdn.microsoft.com/en-us/library/ms141663.aspx .
For expression examples, see SSIS Expression Cheat Sheet (http://pragmaticworks.com/cheatsheet/) and SSIS Expression Examples ( http://social.technet.microsoft.com/wiki/contents/articles/ssis-expression-examples.aspx).
Thanks but that still doesn’t apply to the issue I’m presenting. I wasn’t addressing data types, I was demonstrating the fact that the expression won’t be evaluated without that property of EvaluateAsExpression being set to true.
*edit: rereading comment and realized I think you were just adding more helpful details around the properties of a variable. Thanks for providing more helpful resources and sorry if my last comment came off snappier than I meant to
No problem. 🙂
I have never set the EvaluateAsExpression to true myself but all my expressions work so BIDS Helper must be doing it for me.
Yes, 3rd party tools like BIDS Helper and BIxPress make that change for you automatically. That’s one of their selling points.
Are you sure? I don’t recall we put that in BIDS Helper.
I thought you guys had put that in. I could’ve sworn both our products had that behavior put in. Will need to test it on system that doesn’t have BIxPress on it.
Thanks a lot…
You’re welcome, hope the post helped you out.
In the Properties box for Excel Connection Manager, the HasExpressions parameter is set to False and it is grayed out and will not let me change it to true. How do I change this parameter to True so I can enter an expression?
Mate, six years on and you’ve saved my day. I can’t believe how counter-intuitive SSIS is.