Handle SSIS lookup failure on Date Dimension Date column NULL value

Handle Lookup failure when look up null value in Date dimension.

Problem:

Lookup fail when look up null value in Date dimension.

I have date Dimension table with one row for Null value. In my OLTP Order transaction data some of the date data type column has null value.

I am getting the lookup key not found error and those rows are redirected into reject table. Actually both null was not matched up and not able to found the NULL value dimension key.

Solution:

So in this blog I have explained the customized date look up script with the following screen shots..

Note(Alternate solution):

I don’t want to replace the OLTP date column NULL value with some default date (like 1/1/1900). If I do so in the reporting layer I have to replace the default date (1/1/1900) into NULL. Otherwise this will be misleading in the report also I feel this approach is overhead.

Database & SSIS : SQL Server 2008

Table        ColumnName                            DataType
Date_D    Date(lkp column)                        Date      
Order       ShippingDate(Source column)     Date      

 
select date_Key, date from [dbo].[Date_D]
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 
 
select * from (select date_Key, isnull(date,'1/1/1900') as date from [dbo].[Date_D]) [refTable]

where [refTable].[date] = ISNULL(?,'1/1/1900')
 
 
 
 
 
 
 
 
 
 
   

I am not sure about the performamce of Date dimenison look up. I am working on this...Since its a date dimension volume is not the consideration.

 

I hope this would be helpful to all.