Database Reference
In-Depth Information
to respond to more than one condition ( if / then / el-
seif / then / else ), and unfortunately, the only way to accomplish this
in the SSIS expression language is by nesting conditional operators.
Complex string interrogation or manipulation is required : Simple string
manipulation is easy enough through SSIS expressions with the use of
well-known functions such as SUBSTRING , REPLACE , LEFT/RIGHT ,
UPPER/LOWER , and REVERSE . However, more advanced operations
(extracting text from the middle of a string, replacing multiple patterns
of character(s), extracting numbers embedded in text, etc.) usually re-
quires overly complex expressions. Further, some text operations such
as regular expression (RegEx) matching are not natively supported in
the SSIS expression language.
The logic requires a volatile type cast : Because the SSIS expression
language has no error handling in itself, a conversion that is prone to
failure (text to number, Unicode to ASCII, moving from a larger to
smaller capacity of the same type) may cause an undesired interruption
in your package flow. Often, I'll wrap these into a Script task or Script
component using a TryParse() method or a Try-Catch block, which
allows a greater amount of flexibility in the event of a type cast failure.
The bottom line is that not every ETL challenge within SSIS should be solved using
expressions. The expression language was intended as a lightweight solution, and used
in that context, it is an outstanding supplement to the product line. Try to think of SSIS
expressions as spackle; small, light, elegant, and used pervasively, but in small doses.
As effective as spackle is, a building contractor would never think of building an entire
house using only spackle. As with any tool, expressions in SSIS are best used in proper
context and should not be considered as a one-size-fits-all solution to every problem.
Conclusion
ETL can be hard. Often, it's not the big design problems but the small “how do I . . .?”
tactical questions that collectively cause the most friction during SSIS development.
The SSIS expression language was designed for these types of questions. Its small
footprint, somewhat familiar syntax, and extensive usability across the breadth of SSIS
make it an excellent addition to the capabilities within Integration Services. Used prop-
Search WWH ::




Custom Search