Thursday, August 18, 2005

Integration Services Puzzle

A friend of mine emailed me today and asked how can you get SQL Server 2005 Integration Services to substitue a null to a value (like unknown).

He had been playing around with expressions and was struggling to work out how to make a conditional decision within the expression itself. So without getting to the argument of whether an expression is the correct place to do this, i thought i would take a look.

I have to say it was not immediately obvious to me how you would approach this, however after a little trial and error , the ? operator seems to work nicely and does the trick

(boolean expression?expression 1:expressio2)

So in our case we would have , ISNULL(CUSTOMERNAME)?"Unknown":CUSTOMERNAME

Which provides the desired result.