I'm doing a lookup in SSIS. My original source was an excel spreadsheet. The column I'm looking up contains all null values. This is an optional field in our database and may or may not have values in it. If it does have a value, I want to perform a lookup operation.
The problem I'm running into is that when I perform this lookup as the lookup yielded no results. This lookup has the default cache settings. I've read on technet that if the operation is pre-cached that it handles nulls. I sure doesn't seem like it to me.
I could just ignore error within that operation and the job completes as normal, but I do want it to fail if the column actually has a value that doesn't have a match in the lookup table.
Does anybody know how I can handle this null column? There will be a whole bunch of these columns in this package.