SSIS LookUp is not NULL, for example, docs say it should

Question

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

MSDN docs :

consider the use of full caching, which supports null search operations.

I use full caching (this is the default).

But when I run, I get this error in my zero lines:

The string was not displayed during the search

If I modify the result to ignore no-matches, it works fine. But it ignores all matches. I just want to allow nulls through (like null). Any other mismatch should cause the component to fail.

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

(NOTE: I double-checked the lookup table. It has ALL the values ​​that are in my source table. It just doesn't have a NULL value as a value (because it's weird to have a lookup value of zero.)

Answer 1

I know this is a late answer, but for those who were looking for this, like me, I found this to be the easiest answer:

In a search connection, use an SQL query to retrieve your data and add UNION SELECT NULL, NULL to the end.

For instance:

 SELECT CarId, CarName FROM Cars UNION SELECT NULL, NULL 

The preview shows the optional line CarId = Null and CarName = Null , which will be available in lookpup.

Answer 2

I never noticed this line in BOL in full cache mode. The answer to your problem is what you have already talked about about the presence of NULL in the reference dataset. Quick confirmation of these two sample data streams.

In this example, I generate 4 rows of data: 1, 2, 3 and NULL (Column_isNull = true) and hit the memory table that has all the values ​​and searches between the column in the data stream and c1 defined in the table in memory. It explodes as you described

Then I added another value to the lookup table: NULL and voila, browsing in the full cache may be NULL-NULL.

Removal

To map the input NULL value in the search component, the reference dataset must have the corresponding NULL value and also have a cache mode of FULL.

Answer 3

To work around this issue in SSIS, there is an alternative approach to the previous SO answer that can be applied.

As part of the search conversion, you can redirect rows to an error, and then pass them to another destination, which may simply be the same target destination table in your database.

Therefore, your destination table in the database will still receive all rows (477 in the screenshot below).

Thus, this approach avoids the need to insert dummy NULL values ​​into your lookup table in your database, with tradeoffs:

• An extra step in your SSIS package.
• Error lines (in this case not equal to NULL) will always be loaded into the destination table. To identify these rouge entries, you can export the destination table to a txt file, and then split with the original source file to see the differences.

Answer 4

You can use a conditional delimiter to split the data into two sets, where the given value is null and the rest. Perform an output search with non-zero values, and then combine the results with a dataset that contains zero values ​​using Union All. You can still trap for unsurpassed values ​​in your search and not worry about adding a null entry to your search table