CODEWITHSUNDEEP
ssisdetectconsolidation
ilija veselica
ilija veselica

Reputation: 9574

SSIS check if record was deleted

I am facing a situation where I have built SSIS package for consolidating a table from multiple databases but sometimes DBA manually deletes records from source tables. Is there any way to detect on consolidated table if a record was deleted? I know that I can truncate consolidated table each time before the package runs but I wonder if there's another way?

Upvotes: 1

Views: 1670

Answers (2)

Diego
Diego

Reputation: 36176

I thought of several solutions, but I realize now the big issue is that you don’t have an unified data source, so a record may not exist in A but exist in B, thus you can’t delete it.

So my suggestion is, after you inserted, run a select distinct(id) from all sources and put them together on a structure (table, temp table, view, UNION, whatever..) and run a Merge from this source into destination on the IDs with only a WHEN NOT MATCHED THEN DELETE.

Upvotes: 0

Andy Leonard
Andy Leonard

Reputation: 624

I demonstrate one way to manage deleting data in the destination that has been deleted in the source in the Stairway to Integration Services series at SQL Server Central.

Hope this helps, Andy

Upvotes: 1

Related Questions