Expression Language Patterns - SQL Server Integration Services Design Patterns - page 283

Expressions in SSIS may derive their comparisons or assignments from several
fronts. Built-in system variables permit visibility into software environmental data such
as package and container start times, machine environment information, package ver-
sioning metadata, and more. You can interrogate and manipulate the values of user-
defined SSIS variables by using expressions and access values of package parameters
to be leveraged elsewhere during package execution. In the data flow, expressions may
interact with running data at the cellular level.
Expressions are value-driven at runtime. Unlike settings that are generally only
configurable at design time (think data flow column definitions), expressions will cal-
culate their values when the package is actually executed. Furthermore, a single ex-
pression may be evaluated many times (perhaps with a different result each time) dur-
ing the execution life cycle of the package. Consider the case of the ForEach Loop, a
container that loops through a specified set of objects or values until it reaches the end
of said collection. Expressions that are manipulated within the loop may be updated
dozens or even hundreds of times during this process.
Why Use Expressions?
The ability to use expressions is one of the greatest strengths of SQL Server Integration
Services. Simply put, expressions help to fill in the small gaps. The expression lan-
guage isn't a tool in itself, but rather, it is an interface that helps other SSIS tools more
effectively perform their respective functions. That's all well and good, but in the in-
terests of simplicity, why would an ETL developer choose to use expressions instead of
other languages such as T-SQL, C#, or VB.NET? Here are a few compelling reasons to
employ the expression language in your SSIS packages:
Simplicity : Expressions language can be used to quickly add flow lo-
gic or make small changes to in-pipeline data in the data flow. You
can often handle small ETL changes that might otherwise be releg-
ated to a Script task or component inline without needing to introduce
extra code to the package.
Consistency : Use of the expression language can lead to a consistent
approach to data or program flow challenges. For example, if your
ETL requires that you convert blank strings to NULL s, the approach
and syntax would otherwise be different for flat files, Access data-
bases, and relational database sources. By applying the expression
language to the same task, you reduce the amount of distinct code you