When I am not designing a database, I am usually loading it with data (a good percentage of my time is spent designing data warehouse structures, along with relational ones. Using SSIS is often a challenge, for many reasons, but the most challenging for me is the expression language. Using a Derived Column Transformation, a common task is to turn a NULL value into a surrogate NULL, (we use -1 for UNKNOWN, -2 for Not Applicable). The expression for this prior to SQL Server 2012 was:
(ISNULL(ColumnName) ? "ReplaceWithThisValue" : ColumnName)
Which I looked up every..single..time I used it. "?" means THEN…not IF? ":" means ELSE? Huh? I know this comes from one of those cool languages that I have never mastered, but as I was searching for the syntax again a few days ago, I found REPLACENULL. I had never seen this function before, so I figured I might not be the only one. And perhaps if a commenter feels like telling me how dumb I am to not know about other new expression features I will not be offended. REPLACENULL won't replace every use of the these and the other symbols one must use for SSIS expressions, it does replace one of the more common ones. So what was:
ISNULL(column_dim_key) ? -2 : column_dim_key
Sure it isn't anything more than the Transact-SQL ISNULL function (which itself is oddly named), but it is so much easier to understand, at least for me (and at least one influential Microsoft customer, I imagine.) Now if they will just give us the ability to use a CASE expression!