Express Yourself!

If you've read my blog very often, you know how much I love Expressions in SQL Server Integration Services. Expressions are just about the coolest thing since sliced data[1] and deliver incredible power to SSIS package developers.

But they're not always particularly intuitive. And although they are very powerful, they are not something with which developers interact directly and explicitly, or at least they don't often think of it that way. They think that they're working with precedence constraints or the Derived Column transformation or making task properties dynamic - Expressions just happen to be the tool that makes all of these amazing things possible. This can make it difficult to find help when it's needed, because people aren't searching for "SSIS Expression Syntax" - they're searching on something related to their task at hand.[2]

Fortunately, there are some good resources available online. The first five links below are actually from SQL Server books Online (BOL), so everyone who has SSIS has this information already, but for some reason no one seems to be able to locate them.

Integration Services Expression Reference: This is the top-level topic, the entry point into the expression documentation in BOL.

Integration Services Expression Concepts: This topic describes the main concepts involved with SSIS expressions, including the expression syntax, how to handle data type conversions and the supported literals and identifiers in the expression language.

Integration Services Expression Operators: This topic describes each operator supported by the SSIS expression syntax.

Integration Services Expression Functions: This topic describes each function supported by the SSIS expression syntax. With the exception of the conditional operator this is pretty dry reading, but good stuff to know nonetheless.

Advanced Integration Services Expressions: This is where the BOL content starts to get really good, listing a set of real-world expressions and explaining how they're constructed. Unfortunately, it doesn't quite go far enough.

SQLIS.com Wiki: This excellent community resource picks up where the Advanced Integration Services Expressions page leaves off. It includes a variety of real-world expressions including many more common scenarios and many more samples than are included in BOL. It's delightful, and the information it contains should be part of every SSIS developer's toolbox.

So take a few minutes to see what's out there. The more you know about SSIS expressions the more you're going to love them, and the more powerful you will be when working with SSIS.

[1] Whatever that means...

[2] Which, of course, is why I included the names of those other tasks above - pretty clever, eh? Maybe we'll get more "Windows Live Search" hits that way, eh?