Wednesday, July 09, 2014

SSIS 2012 new REPLACENULL() function

From MSDN documentation – SQL server 2012

REPLACENULL (SSIS Expression)

Returns the value of second expression parameter if the value of first expression parameter is NULL; otherwise, returns the value of first expression.

This function was added to SSIS 2012 and is a useful addition and replaces lengthier expressions needed in SSIS 2008 or 2005.

E.g. after a merge join from 2 data sources A and B, in a conditional split 2 Boolean column values from both tables need to be compared, but sometimes the values on either side can be NULL. In case the value is NULL it is considered to be false.

  • If values where NOT nullable: IsValueA == IsValueB
    • When either has a NULL value the comparison will throw an error
  • with SSIS 2008: (DT_BOOL) (ISNULL(IsValueA) ? false : IsValueA) == (DT_BOOL) (ISNULL(IsValueB) ? false : IsValueB)
  • with SSIS 2012: REPLACENULL(IsValueA, false) == REPLACENULL(IsValueB, false)

Remark : in the above example I have accepted that if IsValueA is null while IsValueB = 0 the values are not technically equal, but in business terms they are.