SSIS - Populating a Variable from an Expression

Published on
14,083 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.

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month