SSIS Expressions == Declarative Goodness!

Developers love declarative things. It's better to use XAML markup to define your UI than to explicitly define control instances and set their properties in your C# code. It's better to use XSLT to transform your XML data into HTML than it is to manually iterate through DOM objects doing it in code. It's better to specify a set-based SQL query than it is to loop through records with a cursor. You get the idea, right?


So why not carry the same motif into the world of SQL Server Integration Services? I've been spending time lately on the SSIS Forums on MSDN, and have noticed that many of the questions being asked can be addressed by the judicious use of SSIS expressions.

What are expressions? Funny you should ask...

According to SQL Server Books Online:

Expressions are a combination of symbols (identifiers, literals, functions, and operators) that yields a single data value. Simple expressions can be a single constant, variable, or function. More frequently, expressions are complex, using multiple operators and functions, and referencing multiple columns and variables.

According to Matthew:

Expressions are a gift from Odin himself, a blessing upon the world of SSIS developers, a magical spear carved from the World Tree itself, with which to bring death unto the plague of static, difficult-to-maintain packages.

Ok, so sometimes I get carried away. Still, expressions are probably the single most powerful tool available to SSIS package developers. SSIS expressions allow you to update the properties of tasks, components, variables, connection managers - pretty much anything in your SSIS package -so that the value of those properties are dynamically evaluated at runtime. Think of expressions as if you could arbitrarily extend the properties of your favorite .NET objects so that when the properties were accessed at runtime your code - and not the code in the property accessor - was run to determine the value to be accessed. It's not exactly what SSIS expressions do, but it's a decent analogy. The key thing to remember is that SSIS expressions allow you to hook up dynamic values to almost any property of almost any component in your SSIS packages so that your packages can respond to changes in their runtime and design time environments. What could be cooler than that?


With all of this said, SSIS expressions in their current form are not perfect. What would make SSIS expressions even better?

• True ubiquity: Currently not all properties of all components (especially data flow components "hidden" within the data flow task) can be bound to expressions.


• Better discoverability: Currently there is no way to tell if a specific property is based on an expression simply by looking at that property in the Visual Studio Properties window. You need to expand out the expressions property node and see if the property is listed there. What we need is some sort of visual indicator next to the properties. Perhaps an icon, perhaps a different text color, but definitely something.


• Better editor support: The current expression editor is definitely a v1 tool, and could use a lot of usability work.

Wow - can you believe how long it took me to finish this post? I started writing it in May, and didn't finish it until September. I think I might be a little busy. Thank goodness for the online expense reporting system I had to use today, which gave me lots of time to work on other things while it did whatever it was doing to take so long for each page to load. ;-)

In any event, back in May I had envisioned a series of articles that each introduced a different problem and how it could be solved by using SSIS expressions. I'm not sure that this series is coming any time soon, but I'll work on it as time permits.

Until then - can you believe there's a post in my Drafts folder that is older than this one?