SSIS Lookup transformation not finding matches

Question

I have a Lookup transformation that does not seem to be finding obvious matches. I have an input file that has 43 records that includes the same CustomerID which is set as an 8 byte-Signed Integer. I am using the Lookup to see if the CustomerID already exist in my destination table. In the destination table the CustomerID is defined as BigInt.

For testing, I truncated the Lookup(destination) table. I have tried all three Cache settings with the same results.

When I run the SSIS package, all 43 records are sent through the No Match Output side. I would think that only the 1st record should go that direction and all others would be considered as a match since they have the same CustomerID. Additionally, if I run the job a second time(without truncating the destination) then they are all flagged as Matched.

It seems as if the cache is not being looked at in the Lookup. Ultimately I want the NO Match records to be written to the Destination table and the Matched records to have further processing.

Any ideas?

Answer 1

Lookup transformation is working as expected. I am not sure what's your understanding of look up is, so I'll go point by point.

For testing, I truncated the Lookup(destination) table. I have tried all three Cache settings with the same results.

When I run the SSIS package, all 43 records are sent through the No Match Output side

Above behavior is expected. After truncate, lookup is essentially trying to find those 43 records within your truncated destination table. Since it can't find any, it is flagging them as new records ie No match output side.

if I run the job a second time(without truncating the destination) then they are all flagged as Matched

In this case, all those 43 records from file are matched within destination table, hence lookup refers them as duplicates and thus they are flagged as Matched output

I am using the Lookup to see if the CustomerID already exist in my destination table

To achieve this, all you need to do is send Matched output to some staging table which can be periodically truncated(as they are duplicate). and all the No match output can be send to your destination table.

You can post screenshot of your lookup as well in case you want further help.

Answer 2

The lookup can't be used this way. SSIS dataflows execute in a transaction. So while the package is running, no rows have been written to the destination until the entire dataflow runs. So regardless of the Cache setting, the new rows being sent to your destination table are not going to be considered by the Lookup while it's running. Then when you run it again, the rows will be considered. This is expected behavior.