Allow NULLS with the lookup transformation?

Hi,

I just wanted to know if there is any way to Allow Null values while doing a lookup on a table in SSIS.

Let me elaborate the situation...

I have a flat file source that has a field called 'code'. I want to lookup in a code table to see if the code in the file is a valid code but the flat file may contain a NULL value as a 'code' (i.e. zero length string which treated as NULL by my package).

My problem is, the SSIS package tries to search for the NULL in the table and the lookup fails and an error is logged as per the business logic but actually NULL is also an acceptable value and the error should not be logged.

I tried inserting a NULL value in the lookup column but that doesn't work. I am not sure but I think I have read somewhere that two null values cannot be compared for equality. I cannot use conditional split to check the null value because I have to use a large number of lookups and a conditional split everywhere will mess up the things.

Is there any way to solve this problem?

try doing an Ignore Failure in the lookup component.|||

Ignore failure will ignore this error for all the values coming in the feed.

I want to ignore only in the case of NULL values. and for values other than NULL (and invalid), it should be redirected to the error output where the error will be logged.

|||Use a conditional split right before the lookup to redirect NULL values. Then after the lookup, you can join the two flows back together.|||Replace the NULL value with an empty string prior to the lookup, add an empty string to your lookup source, then replace the empty string with NULL after the lookup. Or use some other value to represent NULLs, if an empty string won't work. A little kludgy, but it works.|||

yes... It will be a very complex solution. I would probably do that only.

I am just looking for a graceful way to handle this scenario. Please notify me if you come across some links adressing similar issues.