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

One design pattern that I use frequently is to trim out extra whitespace and convert
blank strings to NULL values. As shown in the following, such an operation could be
performed with a single, relatively simple expression:
(LEN(TRIM([Street_Address])) > 0)
? TRIM([Street_Address]) : (DT_WSTR, 100)NULL(DT_WSTR,
100)
Regarding data cleansing using the expression language, I will offer a brief word of
caution: if you find yourself needing to do complex, multistep cleansing operations
within your SSIS packages, consider using some other means to do the heavy lifting.
As I mentioned earlier, the expression language is best suited for lightweight data
cleansing; because complex expressions can be difficult to develop and debug, you
might instead use a richer tool such as the Script task or Script component, or perhaps
Data Quality Services, for these advanced transformations.
Branching
Sometimes you will find that you need to create forks in the road with ETL data flow.
There are several reasons why you might need to create such branches within your data
flow:
Different outputs : For data that exists in a single data flow but is
bound for different destinations, creating branches is an effective
solution. I recall a case I worked on several years back when we were
building a system to distribute data to several financial vendors. Each
of these vendors required the same type of data, but each vendor
would get a different “slice” of the data based on several criteria, and
each required the data in a slightly different format. Rather than
design multiple end-to-end data flows that would essentially duplicate
much of the logic, I created a single package that employed a condi-
tional split transformation to split the data flow based on a specified
condition, and from there, the data branched out to the respective out-
puts.
Inline cleansing : A very common ETL scenario in SSIS is to split
“good” data from “bad” data within a single data flow, attempt to
clean the bad data, and then merge the cleansed data with the good