SSIS LookUp is not dealing with NULLs like the docs say it should

Question

I have an SSIS data flow that uses a lookup. Sometimes the value to be looked up (in my stream, not in the lookup table) is null.

The MSDN Docs say:

consider using full caching, which supports lookup operations on null values.

I am using Full Caching (that is the default).

But when I run I get this error on my null rows:

Row yielded no match during lookup

If I change the result to ignore no-matches then it works fine. But that ignores all no-matches. I just want to allow nulls through (as null). Any other no-match should fail the component.

What am I doing wrong? How can I get nulls to write as nulls, but not ignore any other errors.

(NOTE: I have double checked my look up table. It has ALL the values that are in my source table. It just does not have NULL as a value (because it is weird to have a look up value for null.)

Answer 1

I know this is a late answer, but for anyone searching on this like I was, I found this to be the simplest answer:

In the lookup connection, use a SQL query to retrieve your data and add UNION SELECT NULL, NULL to the bottom.

For example:

SELECT CarId, CarName FROM Cars 
UNION SELECT NULL, NULL

Preview will show an additional row of CarId = Null and CarName = Null that will be available in the lookpup.