SSIS syntax in a derived column expression

Ok So in the picture of my last post I had a Derived Column. This was to see if having a single field with the address in changed the fuzzy lookup. So I add a derived lookup to concatenate the field

However I found most of the data was coming out as null, and I realised nulls concatenated result in NULL and therefore you need to code round it.

So the derived column uses the following bit of code

(ISNULL(address1) ? "" : address1) + (ISNULL(address2) ? "" : address2) + (ISNULL(address3) ? "" : address3) + (ISNULL(address4) ? "" : address4) + (ISNULL(postcode) ? "" : postcode)

A couple of pointers, the ? indicates the question with the : seperating the true and false options. The other is the brackets(braces) you need these to ensure the code works, this gets syntax checked for type safety. If the brackets weren't there you would have a string (address1) being concatenated with a boolean (ISNULL(address2)) etc. Therefore the brackets result in strings being concatenated with strings

-
Published 14 July 2005 09:00 by simonsabin

Comments

12 October 2007 11:22 by Loïc Charpentier

# re: SSIS syntax in a derived column expression

thanks for this example, It was usefull in my data migration for the data transformation.

22 October 2007 14:15 by Holly Kilpatrick

# re: SSIS syntax in a derived column expression

I just wanted to add my thanks also, it is easy for me to get hung up for hours over the syntax of these expressions, for something as simple as adding brackets!