More SQL for Creating NULL Handling SSIS Expressions

by Julie Smith

	
USE AdventureWorksDW2008R2
go

DECLARE @string VARCHAR(MAX)
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
               FROM     INFORMATION_SCHEMA.COLUMNS
               WHERE    TABLE_NAME = @tablename
                        AND COLUMN_NAME NOT IN ( 'ProductKey',
                                                 'ProductAlternateKey',
                                                 '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
                                              FOR
                                                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