SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

Question

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.

Answer 1

Naturally, this is psuedo-code.

SELECT DoctorID
FROM DrTable
where NOT EXISTS (select DoctorID from CSVTable where CSVTable.DoctorID=DrTable))

You could do the update in the same statement using:

UPDATE DrTable

Set IsActive = 0

WHERE Doctorid IN (   SELECT DoctorID
    FROM DrTable
    where NOT EXISTS (select DoctorID from CSVTable where CSVTable.DoctorID=DrTable)))