SSIS：如何检查记录是否不存在于平面文件中但存在于数据库中

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.

我正在准备一个SSIS作业,我将.CVSV文件导入OLE DB目标(sql数据库)。我们将每天获取这些文件。 .CSV文件包含医生的记录。每行代表一名医生。下图显示了我如何成功完成此操作。到目前为止没有问题。

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.

如果医生不再活跃,我们将获得相同的.CSV文件而没有他/她的记录。如何检查记录是否不在.CSV文件中,但它存在于SQL数据库中?我需要在SQL数据库中更新该医生行,并将该行的IsActive字段更新为false。

2 个解决方案

#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)))

#2

-1

Apart from Eric's answer, you could also use the 'merge' function provided by SSIS by default. You would have to make sure that your that your flat file source and the database are both of the same data type with respect to all the columns and they are both sorted over the key that you are using to join them.

除了Eric的回答,您还可以使用SSIS默认提供的“合并”功能。您必须确保您的平面文件源和数据库与所有列的数据类型相同,并且它们都按照您用来加入它们的键进行排序。

#1