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?

    Advertisements

    About ssimagine

    My name is Matthew Roche, and I am a Senior Program Manager with the SQL Server product group at Microsoft. I work on Master Data Services and Data Quality Services, and have previously worked on SQL Server Integration Services. Although I work for Microsoft and will be posting on technical topics, I want to stress that this is a personal blog, and any opinions posted here are mine and mine alone. I built my career around SQL Server and Microsoft technologies for well over a decade before I joined Microsoft as an employee, and I plan on using this blog to share my personal experience and opinions. They may well be shaped by my experience on the SQL Server team, but they’re still mine, and not that of Microsoft, disclaimer, disclaimer, etc., etc..
    This entry was posted in BI, SQL Server, SSIS. Bookmark the permalink.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s