How to prevent SSIS from importing data from a file that already exist in database?

2
votes

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.

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.

3
sqlssis
That's not what ROLLBACK is for. ROLLBACK is used when you start a TRANSACTION and something occurs that means you want to undo everything you did (the opposite of COMMIT). Answers here will hopefully give you programatic methods to achieve this functionality, but it won't be with ROLLBACK. – MatBailie
What do you want to happen? should the duplicates be skipped and the rest inserted, or should the whole batch be rejected? – wildplasser
@wildplasser: entire batch has to be rejected. – user1355868
Well, then read your .csv file into a temp table, then do INSERT INTO target_table(...) SELECT ... FROM temp_table; If there are duplicates, the insert will fail (and the whole batch is ignored) BTW: You are not very clear about your asofdate column. If that is to describe the validity of a single row, why do you want to reject the whole batch? – wildplasser

3 Answers

10
votes

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

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.

Data flow task

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.

Hope that gives you an idea.

Lookup table configuration

Lookup check

2
votes

the correct term is not rollback. Rollback is when you insert something and undo your transaction. you should not insert the data if it already exists.

What you need is a lookup transformation between your DBSource and your DBDestination. This lookup should check if data exists on the destination table and, if not, insert it

0
votes

For this kind of requirement you can make use of Insert TRIGGER on table and when inserting data you can check the data exists in table or not , if you want to check for each and every record

or

Before inserting file in the table you can make use of if exists statment, if you just want to check for the records existsor not like this SQL: If Exists Update Else Insert