SSIS Expressions Basics

For those that are new to SSIS, using expressions may seem very foreign and unpleasant.  Not only is it a new language for you to learn but new data types to understand as well.  If you can get past this unwelcoming feelings you’ll realize using expressions is a great method for making your SSIS packages dynamic.

I will tell you about the basics you need to know before starting to use expressions.  If you’re interested in a crash course on writing expressions read this White Paper by Brian Knight    .  http://www.pragmaticworks.com/downloads/ssisexpressionswhitepaper/

Using expressions isn’t nearly as difficult as many believe.  Provided to you is the Expression Builder that will save many hours of

research on the web trying to determine which function you need for what you’re trying to accomplish.  Luckily you can skip this research step because all the possible functions you can use are located in the Expressions Builder.

There are a variety of places in SSIS where you have the ability to make a package dynamic using expressions.  Connection managers and tasks have options for building expressions to dynamically change the value of a property.  For example, a very common use for expression in connection managers involves using a Foreach Loop Container with the Foreach File Enumerator.  This involves looping through a collection of files and performing the same tasks (usually loading to a table) to each file.  You would use expressions in this case to change the value of the connection string property in the connection manager that stores the file location so after each iteration of the loop you’re loading a new file.

Expressions are not exclusive to connection managers and tasks.  Variables also have the ability to use expression to change how they will evaluate.  Selecting a variable and changing the EvaluateAsExpression property of the variable to True will all for this variable to dynamically change at runtime.  This opens up many possibilities to any object in your package that may want to use variables with.

Of course it must be mentioned that there are Data Flow transforms that require basic knowledge of expressions.  Derived Column and Conditional Split are just a couple of the transforms that require the expressions language to do business.  Using expressions in transforms like these are not necessarily making your package dynamic but they could make your data dynamic.  Expression in transforms can restrict what rows are loaded, change the value in rows, or even replace the value in rows.