Lookup transformation not seeing new records

ssis

I'm trying to load data from one database to another. I am using the lookup transform that looks at the target table to see if the record already exists.

I am getting duplicate data when the package succeeds.

Why doesn't the lookup transform refresh the table it's looking at every time it is being run?

Any ideas on how to create the lookup transformation so that it will refresh its connection to the table?

I want to keep the records I already have, and add any new records but the package should not load any duplicates.

How do I make sure not to get duplicated data by making sure the lookup transformation sees the newly added data immediately?

Best Answer

By default, the lookup transformation uses full cache and doesn't refresh it's data. You would need to change the lookup transformation to "No Cache" to make sure it executes a query every time the task has to look up a value:

enter image description here

Please refer to the guidance on the SSIS team blog:

Lookup cache modes

When to use no cache:

  • When your reference table is changing (inserts, updates, deletes)
  • ...
Related Question