Clever Geek Handbook

SSIS: how to check if a record is in a flat file but exists in the database

I am working on preparing an SSIS job where I import a .CVSV file to an OLE DB destination (sql database). We are going to receive these files on a daily basis. The .CSV file contains records of doctors. Each line represents a doctor. Below is how I can do this successfully. No problem up to this point.enter image description here

This is where I need help:

If the doctor is no longer active, we are going to get the same .CSV file without writing him / her. How can I check if there is an entry in the .CSV file but it exists in the SQL database? I need to update this doctor row in SQL database and update the IsActive field for that row to false .

+3


source to share


2 answers


Naturally, this is psuedo-code.

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


You can do an 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)))
+1


source


Besides Eric's answer, you can also use the 'merge' function provided by SSIS by default. You must make sure that your flat file source and database are the same datatype with respect to all columns, and both are sorted by the key you use to join them.



-1


source






More articles:


All Articles