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.
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?