Null values in SSIS Lookup #sqlhelp

So I’m dusting off the cobwebs in an attempt to either change my position here, or sharpen up my skills to move somewhere else.  The best way to do that is by just doing it.  I thought I would put my efforts to use and possibly benefit my company so I’m putting together a small DW to build a proof of concept at work.  Still trying to do more BI than mgmt, but ‘they just keep pulling me back in’!

So I have a situation I’m faced with and I think I know how to handle, but I’m curious to see what the SQLFamily has to say.

If you use the practice of soft delete and just mark fields deleted instead of actually getting rid of them, you have rows with values in this column, and rows that don’t.  Common practice(i think) in a dw is to do a lookup to convert date values to int values against a Date Dimension.  So if you do a straight lookup at that column, it fails because it can’t compare Null to an int.

First question – Why not?  Is it because technically Null is not a value or is it because Null doesn’t exist in my DimDate?  Pretty sure it’s the latter, but I want to know for sure.

So an alternative is to change the error handling in the lookup to ignore it.  The issue there is, it ignores the record, so in this case, I’d only get all of the records that have a value present for that column.  I need them all, regardless of null or date value.

Second question – if I don’t want to fail, but I still want the record, I don’t want to redirect them do I?  I was thinking I might just redirect it to the next step along with the ones that pass, but that seemed a bit weird.

The other thing I can do is to modify the null values to either have some way off future date – 20990101, or use the Unknown value in the DimDate table and set them all to 0, either in a derived column or in the query isNull(deleteDate, 0).

Is saving a null to a datawarehouse something that should always be avoided?  I’m attempting to flesh out my total BI knowledge instead of just sticking to the presentation and cube stuff.  I’ve done a bunch of SSIS before, but it’s been a while.

If you have a resource that you can direct people to for SSIS Best Practices, or BI Stuff every moron should know, i’d appreciate it 🙂