What are expressions, where are they used and How to use expressions for variables in SSIS?

Problem: What are expressions, where are they used and How to use expressions for variables in SSIS?

Solution:   Expressions

An expression is a combination of symbols—identifiers, literals, functions, and operators—that yields a single data value. Simple expressions can be a single constant, variable, or function.

More frequently, expressions are complex, using multiple operators and functions and referencing multiple columns and variables.

Use case of expressions in SSIS:

      ·         Dynamic connection managers
·         For loop iterations
·         Conditional precedence constraints.

Example:

In the example, I would like to show we can use expressions to export a file with today’s date dynamically appended to the file name.

Step 0: Create a variable date as string and set the scope to package. Create an expression for setting the default values as today’s date.

Step 1: Create a dataflow task, choose ole db source and select a local connection and reference a table as source and connect the destination to flat file and match the columns. 

Step 2: Now move on to the properties of the flat file connection string from the expressions window in the properties of flat file connection manager

after clicking on the ellipsis you can choose the connect string property form the drop down and then set the expression. 

I am using the expression of export date as a variable and this can be appended with file naming conventions and folder structures, as you can see the back slash are double times as SSIS escapes one so we keep double back slashes.

Evaluate expression to get the format as you expected and hit ok.

Step 3: Execute the task.

Output:

As you can see the file has been exported to destination directory with today’s date stamp.