<

SSIS - Populating a Variable from an Expression

Published on
14,423 Points
8,423 Views
Last Modified:
Approved
Community Pick
Expressions are in essence, code. If you had an expression 2 + 2 it would evaluate to 4. A string expression of “Hello” + “ “ + “World” would evaluate to “Hello World”.

Expressions are an excellent choice for working with dynamic variables, especially when the value may change more than once during runtime, an expression evaluates the value each time it is referenced in the package. Expressions can combine other variables together to form a single variable.

The purpose of this example is not to teach expression building so I will limit the complexity.

To set a variable by an expression.
 

1. Select the Variable

Select the variable in the Variable Windowselectthevariable.png
 

2. Properties

Go to the Properties Window. Set EvaluateAsExpression to True and click on the elipse on the Expression line to open the Expression Builder.evaluateasexpression.jpg
 

3. Expression Builder

Click on the elipse and open the Expression Builder.ssisexpressionstep3.jpg

In the Expression Builder you can choose from Variables and Functions to create a variable. Since this is a string variable I also used the DT_WSTR function to convert the GETDATE() function to a string. Pressing on Evaluate Expression will let you test your results. If you get an error it will not let you press OK and save, it has to be error-free. I wish that was different.

Here is another expression that adds the date in a filename format.
expressiondate.png
Note, when I press OK on this I get an error.
expressionerror.png
Since we are not working with a Date datatype, need to convert the Date functions to a string.
expressiondatetostring.png

The final product is a variable which is evaluated each time it is called in your SSIS Package.
variableevaluated.png

So, go ahead and start using expressions in your packages.
0
Author:Steve Hogg
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free