SQLChicken.com

SQL Server DBA Tips & Tricks

By

Monday Morning Mistakes: SSIS Expressions Not Evaluating Correctly

M3logo

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.

image

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.

image

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

image

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.

image

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

image

Share
  • http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx Carla Sabotta

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

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      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

      • http://msdn.microsoft.com/en-us/sqlserver/cc511477.aspx Carla Sabotta

        No problem. :-)

  • Gary Wilson

    I have never set the EvaluateAsExpression to true myself but all my expressions work so BIDS Helper must be doing it for me.

    • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

      Yes, 3rd party tools like BIDS Helper and BIxPress make that change for you automatically. That’s one of their selling points.

      • http://artisconsulting.com/blogs/greggalloway/default.aspx Greg Galloway

        Are you sure? I don’t recall we put that in BIDS Helper.

        • http://emailtoid.net/i/79a23b74/bec3ed36/ Jorge Segarra

          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.

  • anjali

    Thanks a lot…

    • SQLChicken

      You’re welcome, hope the post helped you out.