2

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?

  • 1
    Q: Why doesn't the lookup transform refresh the table it's looking at every time it is being run? A: Because you didn't create it that way? – SQL_Underworld Jan 18 '17 at 1:03
  • 1
    You need to work out which records you already have and not load them. One (inefficient) way to do this is to us a lookup and match on a unique key then use a conditional split to only insert non matching records – Nick.McDermaid Jan 18 '17 at 6:37
4

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)
  • ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.