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

slightly from that of traditional ETL and is typically branded as ELT
(extract/load/transform). Using this model, you could use the Execute
SQL task to transform the data once it has been loaded from the source
to the relational database in which it will be transformed.
• Script task : When working in the control flow, you can substitute an in-
stance of the Script task in place of an overly complex SSIS expression.
When using a Script task for this purpose, you get the added benefits of
IntelliSense, error handling, multistep operations, and the ability to in-
clude comments in your code.
• Script component : Replaces complex expressions within the data flow,
for the same reasons as stated previously. In addition, the Script com-
ponent may be used as a source, transformation, or destination in the
data flow surface, giving you even greater control of the manipulation
of data than by strictly using expressions.
• Custom task/component : If you find yourself reusing the same com-
plex logic in many packages, consider creating a custom task or com-
ponent that you can distribute to multiple packages without having to
copy and paste script code to each package.
• Third-party task/component : Sometimes it's easier to buy (or borrow)
than to build. There are hundreds, perhaps even thousands, of third-
party tasks and components designed to extend the native behaviors of
SSIS. In fact, many of these tools are freely available—often with the
underlying source code in case you need to further customize the beha-
vior of the tool.
There are no hard-and-fast rules defining when an expression may not
be the best solution. However, there are a few design patterns that I tend
to follow when deciding whether to use an expression or some other
tool when applying dynamic logic in my SSIS packages. Typically, I
will avoid using expressions in situations where
• The expression will be exceptionally lengthy : If the logic required in
an expression would exceed more than a few hundred characters, a
script or other tool is often a better choice.
• The expression requires more than three levels of nesting : Especially
in cases where If/Then/Else logic is required, there's frequently a need