SSIS: How to check if a record DOESN'T exist in flat file but exists on the database
How do I check to see if the record is not in .CSV file but it exists in SQL database?


Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a particuliar record is in the staging table or not.

This way you get better control of the process and only update the main table or add new records to the main table. You can do it using TSQL (Merge) or SSIS.

This is what we do and it incurs less overhead on the main table, since you will update or add only records that have changed.

Also, I really like the idea of doing a soft delete IsActive = TRUE or FALSE. This way you can preserve the history as well.