I am working on a few SSIS packages translating data from our enterprise source system database to our new website database. Our source system deletes data from tables quite often. Because the data exists on the website from previous package executions, the deleted rows either needs to be deactivated or deleted on the website accordingly (depending on which tables have the active flags to retain deleted data).
What is the recommended way of accomplishing this?
I am trying to avoid having to run the source query twice to our enterprise database for every package and execute two different Data Flow Tasks. Furthermore, the databases are not, and cannot, be linked to do cross-database queries. I am also trying to avoid truncating tables and re-syncing each time as there is a lot of data being moved and the website will be pretty active, not to mention there may be data to integrate the other direction in these tables.
Thank you for your time and expertise,