如何防止 SSIS 从数据库中已存在的文件中导入数据? [英] How to prevent SSIS from importing data from a file that already exist in database?

问题描述

每天我都会将一些 csv 数据加载到我的表中.我的表有日期列，现在我需要使用回滚函数，这样，如果数据已经存在于我的表中，它必须回滚，否则它必须根据我的 Asofdate 参数插入.

Everyday i load some csv data into my table. And my table has date column, now i need to use rollback function, such that, if the data already exists in my table it has to rollback, else it has to inserts based on my Asofdate parameter.

我知道这有点令人困惑，让我更清楚一点.

I know it is little confusing, let me be more clear.

我的表有eid，ename，asofdate列，现在我每天插入新文件时，它必须根据asofdate查看该文件是否已经存在于该表中，如果数据存在，它应该回滚数据.

my table has eid, ename, asofdate columns , now when i insert new file every day, it has to see whether that file is already existed in that table based on asofdate, if data exists, it should rollback the data.

推荐答案

正如@Diego 所提到的，您将在数据流任务中使用 Lookup 转换 来实现该功能.

As @Diego had mentioned, you would use a Lookup transformation within the Data Flow task to achieve the functionality.

您的数据流任务看起来像这样.在这里，平面文件源读取 CSV 文件，然后将数据传递给查找转换.此转换将检查目标表中的现有数据(假设表名为 dbo.Destination).查找转换的配置显示在下一个屏幕截图中.如果没有匹配的记录，则将来自 CSV 文件的数据发送到 OLE DB Destination，否则数据将被丢弃.

Your data flow task would look something like this. Here, the Flat file source reads the CSV file and then passes the data to Lookup transformation. This transformation will check for existing data in the destination table (say the table name is dbo.Destination). The configuration for Lookup transformation is shown in the next screenshots. If there are no matching records, then the data from CSV file will be sent to the OLE DB Destination otherwise, the data will be discarded.

在 Lookup 转换 中，您将在 Connection 选项卡上选择目标数据库表.在列部分，您将验证要检查现有数据的所有列.在这种情况下，来自 CSV 文件的列 eid、name 和 asofdate 将根据数据库表 dbo 中的同名列进行验证.目的地.如果这三列的传入值与表中的任何行匹配，则数据将不会进一步向下发送.

In the Lookup transformation, you will choose the destination database table on the Connection tab. On the Columns section, you will verify all the columns that you would like to check for existing data. Here in this case, the columns eid, name and asofdate coming from CSV file are validated against columns of same names in the database table dbo.Destination. If the incoming values for these three columns match with any rows in the table, the data will not be sent further down the stream.

希望能给你一个想法.

这篇关于如何防止 SSIS 从数据库中已存在的文件中导入数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！