Whilst reading posts on the SSIS forum of late it has become evident that people are unaware of the power that expressions can provide within an SSIS package. This is understandable - alot of people are starting to use SSIS now but expressions aren't the most obvious feature.
So what are they? Well the most generic explanation is that expressions allow your packages to be dynamic by setting properties of objects at runtime. What are they used for? Well there are a number of scenarios, the most common being:
- Dynamically setting the connection string of external connections
- Conditional precedence constraints
- Setting the location of raw files
- An alternative to parameterised SQL statements in the Execute SQL Task or a source adapter
The reason that expressions aren't the most visually obvious feature is they're usually hidden away in the properties pane. Lets demonstrate that in one of the scenarios already mentioned - dynamically setting the connection string of external connections.
Imagine the following scenario:
- You have a data-flow that loads data from a CSV file.
- That data-flow is within a ForEach loop that iterates over a collection of CSV files.
- The ForEach Loop populates a variable with the name and location of the file
In other words your package looks something like this:
On each loop iteration @[FileNameAndLocation] will, not surprisingly, store the name and location of the current file.
Now, we need to make sure that the data-flow is going to process that file. We do that by dynamically setting the ConnectionString property of "FlatFileConnectionManager" using an expression. To do that select "FlatFileConnectionManager" and press F4. The properties pane will appear in which you will be able to see the entry point to the connection manager's expression collection:
Clicking the ellipsis will display the Property Expressions Editor dialog from which we select the ConnectionString property.
Thereafter we go through to the Expression Editor which is where we set up an expression that will dynamically set the ConnectionString property. In this case we use the @[FileNameAndLocation] variable:
As you can see from the evaluated value, the ConnectionString property will be set to the name of the variable. This means that on every iteration of the ForEach loop the file that the "FlatFileConnectionManager" will be pointing to to the currently iterated file.
This is only one use of expressions but hopefully through this simple scenario we've demonstrated how expressions are set and how they can be used.
Remember that expressions are not only available to be set on connection managers, they can be set on variables, task properties and precedence constraints as well. You'll find that as you begin to build ever more complicated solutions you will start to use expressions more and more.
-Jamie