[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More


SSIS - Populating a Variable from an Expression

Published on
14,243 Points
Last Modified:
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.
Note, when I press OK on this I get an error.
Since we are not working with a Date datatype, need to convert the Date functions to a string.

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

So, go ahead and start using expressions in your packages.
Author:Steve Hogg

Featured Post

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month