This post is part of a series on this blog that will help me, and hopefully you, pass exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012. So far, we’ve covered:
- Tables and Schemas in the data warehouse
- Dimensions and Slowly Changing Dimensions
- Fact tables and measures
- Intro to columnstore indexes
- Columnstore indexes and partitioning
- Introduction to SQL Server Data Tools (SSDT)
- Connection Managers
- Control Flow Tasks and Containers
- Data Flow Source Adapters
- Data Flow Destination Adapters
- Data Flow Transformations
- Variables and Parameters
In this installment we build on our knowledge of variables and parameters and put them to use in SSIS expressions.
Intro to Expressions
I mentioned earlier that variables can help make your packages more dynamic and reusable. This is largely possible through the use of variables in expressions. An expression is a combination of variables, constants, operators, and functions that are used to yield a value at run time. At its simplest, an expression can be a single variable or constant. But more often, you’ll build more complex expressions as part of your packages.
Some components in SSIS require the use of expressions. For example, the Conditional Split transformation requires expressions to help direct rows to different output streams. The For Loop container will typically use expressions to set the initial looping counter value, increment that value, and evaluate whether the loop should continue to iterate.
Precedence Constraint Expressions
Expressions can also be used in precedence constraints to dynamically determine, at runtime, whether a particular task should be executed. By default, the evaluation operation property of precedence constraint is set to “Constraint”. However, you also have the option of using an expression, “expression and constraint”, or “expression or constraint”. I’ve used expressions in precedence constraints to dynamically configure the number of parallel data flow operations I want to execute, based on the hardware of the server I’m running on.
Property Expressions
Property expressions are assigned to the property of an SSIS object to dynamically update that property at runtime. A common use of property expressions is for connection managers used within a Foreach Loop that loads flat files. For each iteration of the loop, the ConnectionString property of the Flat File Connection Manager can be changed to point to the current file being processed.
Writing Expressions
The SSIS expression syntax is similar to that of C++ and C#. So if you’re familiar with those languages, you should have no trouble writing expressions in SSIS. Expressions can be written directly in the dialog box of the object you’re editing, or you can click the ellipsis button next to the expression field to open the Expression Builder dialog box. There you’ll see all of the functions and variables available to you, and be able to view the evaluated value (so you can see whether you’ve got it right or totally mucked it up).
Handy tool or instrument of torture?
Until you’ve gotten some experience with the syntax, there will be times of frustration trying to figure out why the heck this @$S&%@%!^ expression isn’t working. Just remember, Google and bourbon are your friends.
Additional Resources
For more information on SSIS expressions, check out the following resources:
