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

which data is manipulated, extrapolated, or discarded based on custom logic that is spe-
cific to the business at hand. These rules may be general enough to apply to an entire
industry (healthcare billing workflows, for example) or as specific as the arrangement
of data to suit the preferences of an individual manager.
Generally speaking, the use of expressions to apply business logic works best when
limited to a small number of simple business rule cases. As mentioned earlier, the ex-
pression language is not ideal for multiple test conditions and therefore may not be
ideally suited for multifaceted and complex business rules. For enterprise-level busi-
ness rule application, consider other tools in SSIS, such as the Script component or Ex-
ecute SQL task (for operations that can be performed at the relational database level),
or perhaps a separate tool such as SQL Server Data Quality Services or Master Data
Services.
CHOOSING BETWEEN COMPLEX EXPRESSIONS AND
OTHER TOOLS
In my experience, the majority of uses of SSIS expressions involve short, simple
expressions. Interrogating the value of a variable, modifying the contents of an ex-
isting column, comparing two values, and other similar operations tend to require
relatively brief and uncomplicated logic as an SSIS expression. However, there
are many cases where a short-and-sweet expression just won't get it done.
In these cases of more complicated logic, is an SSIS expression still the best
choice? In some instances, the answer is no. As mentioned earlier, there are in-
stances in the ETL cycle where the expression language is ill-suited to solve the
problem. In cases where the logic required involves complexity that exceeds that
which is practical or convenient for the SSIS expression language, a common pat-
tern is to engage a separate tool to address the problem at hand. Some of the other
methods for handling these complex logical scenarios are as follows:
• Data source component : Especially when working with relational
source data, it can be simpler and faster (both in design time and
runtime) to build the necessary logic into the source component instead
of using an expression in SSIS.
• Execute SQL task : Sometimes it's easier to load the data to a relational
store and then perform transformation and cleansing there rather than
doing it inline within the SSIS package. This methodology differs