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

The SSIS expression language is an interpreted language that has been built into the
SSIS runtime environment. This specialized language is used to craft scalar-valued
snippets of code (individually referred to as expressions ) that you may use at various
points within the SSIS environment.
The SSIS designer exposes dozens of interfaces where expressions can be used in
place of hard-coded values, allowing the BI professional to leverage that flexibility to
create dynamic and reusable elements within SSIS. Conceptually, it's not unlike the
product-specific dialects that exist in other Microsoft development environments. For
example, when you are developing reports in SSDT or BIDS that you want to deploy to
SQL Server Reporting Services, you can use Visual Basic for Applications (VBA) code
to generate dynamic behavior during report execution and rendering.
As you explore the expression language, you'll find it to be a very powerful addi-
tion to the natural capabilities of SQL Server Integration Services. It has a rich library
of functionality that will be familiar to both developers and DBAs. Among the func-
tional domains of the SSIS expression language are
• A full complement of mathematical functions and operators
• An impressive set of string functions that may be used in comparisons,
analysis, and value manipulation
• Common date and time functionality, including date part extraction, date
arithmetic, and comparison
The expression language serves two different roles within the package life cycle:
Evaluation : You can use expressions to determine whether a speci-
fied condition is true and to change the behavior of the package ac-
cordingly. When it is used as part of the control flow, an expression
used as an evaluation may check a certain value and dynamically alter
the execution path based on the results of that comparison. Within the
data flow, expressions allow you to evaluate data a row at a time to
determine how to proceed in the ETL.
Assignment : In addition to using expressions as decision-making ele-
ments, you can use SSIS expressions to programmatically modify
data during package execution. Typically you might use them as
expression-based property settings and to transform in-process data
within the data flow.