SSIS: How to check if a record DOESN'T exist in flat file but exists on the database I am working on preparing an SSIS job where I am importing a .CVSV file to OLE DB destination (sql database). We are going to get these files on daily basis. The .CSV file contains records of doctors. Each row represents a doctor. Below image shows how I am able to do this successfully. No problems upto this point. 
 

Here's what I need help with:

If the doctor is no longer active we are going to get the same .CSV file without the record of him/her. How do I check to see if the record is not in .CSV file but it exists in SQL database? I need to update that doctor row in SQL database and update the IsActive field for that row to false.
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.