More SQL for Creating NULL Handling SSIS Expressions

by Julie Smith

USE AdventureWorksDW2008R2

DECLARE @tablename VARCHAR(50)
SET @tablename = 'DimProd';
WITH    cte
        AS ( SELECT   '((ISNULL(' + COLUMN_NAME + ') != ISNULL(lkp'
                        + Column_Name + ')) || ((ISNULL(' + COLUMN_NAME
                        + ') == FALSE && ISNULL(lkp' + COLUMN_NAME
                        + ') == FALSE ) && (' + COLUMN_NAME + ' != lkp'
                        + COLUMN_NAME + ')))' AS DeltaConditionalSplitExpression
               WHERE    TABLE_NAME = @tablename
                        AND COLUMN_NAME NOT IN ( 'ProductKey',
                                                 'Status', 'EndDate',
                                                 'StartDate' )  --Here and the below 
							--line exclude any columns you are not using.
                        AND data_type NOT LIKE '%binary%'
    SELECT  @string = REPLACE(( SELECT  STUFF(  --Stuff gets rid of the very first pipe we had to put in 
							--to the expression using the black box technique.
                                              ( SELECT  '|| '
                                                        + [DeltaConditionalSplitExpression] AS [text()]
                                                FROM    cte
                                                XML PATH('')
                                              ), 1, 3, '')
                              ), 'amp;', '') --have to REPLACE the 'amp;' with '' 
                              --the xml parser is going to     	--add for your && s 
	SELECT  @String AS DeltaConditionExpression ,
        LEN(@string) AS LengthOfExpression

This expression returns true if one side is null and the other is not, or if both sides are not null and the sides then do not equal.
It also does not make the conditional split fail if there are nulls in the data flow.
So for one column:
((ISNULL(Color) != ISNULL(lkpColor)) || ((ISNULL(Color) == FALSE && ISNULL(lkpColor) == FALSE ) && (Color != lkpColor)))
breaking it down. is either side null while the other is not? yes? then fire
are both sides not null? yes? then are they unequal? yes? then fire.
This SQL concatenates all the column expressions together, puts the || in between them all and gives you the entire expression which you can paste into your Delta Condition
inside your conditional split.
I like this one much better than the first one I wrote. Doh!

Leave a Reply