CODEWITHSUNDEEP
sql-serverssis
Rajaram Shelar
Rajaram Shelar

Reputation: 7897

How to check in ssis if record already exists

How to check in SSIS if particular record(primary key column) is already exists in a destination table, update if it is present else insert it.

I gone through searching on net for several hours but could not find exact solution. Please suggest the solution.

Upvotes: 1

Views: 15231

Answers (3)

Omkar Naik
Omkar Naik

Reputation: 51

First we need to check records is available in Destination using lookup transformation. If receive records is not matched with existing data then it's new records else it's old.

Upvotes: 0

Mike Honey
Mike Honey

Reputation: 15037

Here's an alternative - a lot less code and runs very quickly.

First load a Lookup Cache using a Cache Transform with any of the columns you want to preserve (at a minimum, the surrogate key and business key) from the target table. Then truncate the target table. Then at the end of your main Data Flow, add a Lookup using the Cache, joined on your Business Key, and return all the columns you want to preserve. Match Output goes to an OLE DB Destination with Keep identity checked (to preserve your Surrogate Keys). No Match Output goes to a separate OLE DB Destination. Both destinations must have Table lock unchecked (to avoid deadlocks), but can use fast load.

Upvotes: 0

Samuel Vanga
Samuel Vanga

Reputation: 501

Some examples from the internet: Anatomy of incremental load

Another example

These should give you detailed examples.

Upvotes: 4

Related Questions