THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Quickie - SSIS Expression Function - REPLACENULL

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


REPLACENULL(column_dim_key, -2)

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!

Published Thursday, December 22, 2016 9:55 PM by drsql
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



andyleonard said:

You're not dumb, Louis. ReplaceNull is an awesome SSIS Expression Language function!


December 23, 2016 9:18 PM

Andy Hogg said:

I didn't know this, thanks for posting.

At last, some intuitive SSIS Expressions syntax!

December 24, 2016 11:37 AM

Nathan said:

I've learned something new

December 26, 2016 8:42 AM

drsql said:

Glad to see at least a few people didn't know about it. I felt pretty excited when I thought it was a 2016 new feature. Then really dumb that I had missed it for at least the last two years of using SSIS 2014 :)

January 17, 2017 8:55 PM

Joe said:

good feature - since 2012

February 1, 2017 3:28 AM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement