Community Pick: Many members of our community have endorsed this article.

SSIS - Populating a Variable from an Expression

Steve HoggIT
CERTIFIED EXPERT
Published:
Updated:
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 Window selectthevariable.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
8,770 Views
Steve HoggIT
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.