CODEWITHSUNDEEP
sql-serverssisintegration
Bart
Bart

Reputation: 25

SSIS no-match lookup? SQL server integration services - prevent duplicate rows

In ssis 2012, let's presume I simply copy customer data from one DB Source to a DB Destination (both are different database instances, one cannot "see" the other). How do I prevent adding customer data I already added before. In other words, when I rerun the task, it should not add the customer twice or more (only the ones that previously failed). We have a non-unique reference available in the destination customer table e.g. 'SourceCustomerID' which is non-unique! So we cannot rely on some unique index in the Destination table(s), and if we could, I don't want go this way (would cause failures)...

Added based on questions below: there ARE columns that uniquely identify data in the target table, and we need these for this, but these are nor implemented as unique indexes, nor do I want to let the job (or rows) fail like this. I want to prevent adding these rows in a controlled way.

I tried the lookup component, playing with "Lookup No Match Output", etc...no luck yet.

Any ideas how to accomplish this using the SSIS principles??

Best regards Bart.

Upvotes: 0

Views: 524

Answers (1)

dbmuller
dbmuller

Reputation: 338

Use the SCD component https://msdn.microsoft.com/en-us/library/ms141715.aspx

You map the business key which will check for existing record and you can insert/update. You can alter it to insert only.

Upvotes: 1

Related Questions