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

Note There was a limit on string size in SQL Server 2008 Release 2 that's been
greatly relaxed in 2012.
Data Flow Expressions
As we move from the control flow into the data flow, we find the more traditional use
of expressions as part of our ETL strategy. Like the higher-level executables, we find
that every component in the data flow is affected either directly or indirectly by SSIS
expressions.
Data Cleansing
Lightweight data cleansing is one of the most common uses of the expression language
within the data flow of SSIS. Most frequently used within the derived column trans-
formation, expressions can be used for certain cleanup tasks, including these:
• Changing the case of data
• Grabbing a substring from within a longer string
• Trimming extraneous space characters
• Replacing inappropriate characters (such as removing letters from text)
• Changing data length or type
Often, you can minimize the need for data cleansing in the data flow simply
through well-designed query statements in the extraction from the various data sources.
However, sometimes cleanup at the source is just not an option. Many sources of data
are nonrelational: consider text files and web services as data sources, for example,
which generally do not have the option of cleaning up the data before its arrival into
the SSIS space. Sometimes even relational sources fit in this box: I've encountered a
number of scenarios where the only interface to the data was through a predefined
stored procedure that could neither be inspected nor changed by the ETL developer.
For cases such as these where source cleansing is not possible, using expressions with-
in the data flow is a good second-level defense.