SQL for SSIS Expression Building

This is a sub post where you can just copy the SQL from my post on Incremental Load patterns, how to use SQL Metadata to create complex expressions: HERE

–cheers Julie

Line by Line


USE AdventureWorksDW2008R2
go

DECLARE @tablename VARCHAR(50)
SET @tablename = 'DimProd'
 
 
SELECT DISTINCT
      ForExpression = 
        CASE WHEN DATA_TYPE LIKE '%date%'  --NULL date will be replaced with 1/1/1900 
             THEN '(ISNULL(lkp' + COLUMN_NAME
                  + ') ? (DT_DATE)"1/1/1900" : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME
                  + ') ? (DT_DATE)"1/1/1900" : ' + COLUMN_NAME
                  + ')    '
             WHEN DATA_TYPE LIKE '%char%' ---NULL CHAR, VARCHAR, NCHAR, NVARCHAR will be 
											--replaced with Empty String
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? "" : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? "" : ' + COLUMN_NAME
                  + ')   '
             WHEN DATA_TYPE LIKE '%bit%' --- bit data types will use FALSE for default.
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? FALSE : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? FALSE : ' + COLUMN_NAME
                  + ')     '
             WHEN NUMERIC_PRECISION IS NOT NULL  ---this condition captures all numeric data types which 
													--require the default null comparison to be 0.
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? 0 : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? 0 : ' + COLUMN_NAME
                  + ')   '
        END  
		FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @tablename
        AND COLUMN_NAME NOT IN ( 'ProductKey', 'ProductAlternateKey', 'StartDate', 'EndDate', 'Status' )  
        AND DATA_TYPE NOT LIKE '%binary%'  --Here and the above line exclude any columns you are not using.

BlackBox, gives you the whole Line for Conditional Split

DECLARE @string varchar(max)  DECLARE @tablename VARCHAR(50)
SET @tablename = 'DimProd'
    ; with cte as ( 
 SELECT DISTINCT
      ForExpression = 
        CASE WHEN DATA_TYPE LIKE '%date%'  --NULL date will be replaced with 1/1/1900 
             THEN '(ISNULL(lkp' + COLUMN_NAME
                  + ') ? (DT_DATE)"1/1/1900" : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME
                  + ') ? (DT_DATE)"1/1/1900" : ' + COLUMN_NAME
                  + ')    '
             WHEN DATA_TYPE LIKE '%char%' ---NULL CHAR, VARCHAR, NCHAR, NVARCHAR will be replaced with Empty String
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? "" : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? "" : ' + COLUMN_NAME
                  + ')   '
             WHEN DATA_TYPE LIKE '%bit%' --- bit data types will use FALSE for default.
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? FALSE : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? FALSE : ' + COLUMN_NAME
                  + ')     '
             WHEN NUMERIC_PRECISION IS NOT NULL  
			 ---this condition captures all numeric data types which require the default null comparison to be 0.
             THEN '(ISNULL(lkp' + COLUMN_NAME + ') ? 0 : lkp' + COLUMN_NAME
                  + ') != (ISNULL(' + COLUMN_NAME + ') ? 0 : ' + COLUMN_NAME
                  + ')   '         END  
		FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   TABLE_NAME = @tablename
        AND COLUMN_NAME NOT IN ( 'ProductKey', 'ProductAlternateKey', 'StartDate', 'EndDate', 'Status' )  
        AND DATA_TYPE NOT LIKE '%binary%'  
		)
		 select  @string = (SELECT stuff(  ---Stuff gets rid of the very first pipe we had to put in to the expression using this technique.
	  (SELECT '|| ' + [ForExpression]  AS [text()]
	        FROM cte  
	  FOR XML PATH('')),1,3,''))
		SELECT @String as DeltaConditionExpression 	, LowerThan4000PassFail = case when len(@string) < = 4000 then 'PASS' else 'FAIL' end
		Print 'If LengthOfExpression > 4000, SSIS will not accept it'
		

Leave a Reply