how to avoid re-inserting data into sql table while re-running SSIS package that loads data from flat file to SQL table?

Question

I have a flat file with the following data

Id,FirstName,LastName,Address,PhoneNumber
1,ben,afflick,xyz Address,5014123746
3,christina,smith,test address,111000110
1,ben,afflick,xyz Address,5014123746
3,christina,smith,test address,111000110
4,nash,gordon,charlotte NC ADDRESS,111200110

I have created a SSIS package that has flat file source , and an aggregate function that makes sure that only unique rows are inserted and not duplicate records from the flat file , and SQL table as my destination.

Everything is fine when i run the package , i am getting below output in SQL table

Id  FName   LName   Address phoneNumber
1   ben afflick xyz Address 5014123746
4   nash    gordon  charlotte NC ADDRESS    111200110
3   christina   smith   test address    111000110

But when i add some new data to the flat file as below

Id,FirstName,LastName,Address,PhoneNumber
1,ben,afflick,xyz Address,5014123746
3,christina,smith,test address,111000110
1,ben,afflick,xyz Address,5014123746
3,christina,smith,test address,111000110
4,nash,gordon,charlotte NC ADDRESS,111200110
5,abc,xyz,New York,9999988888

and re-run the package the data that is already present in the table is getting re-inserted as below

1   ben afflick xyz Address 5014123746
4   nash    gordon  charlotte NC ADDRESS    111200110
3   christina   smith   test address    111000110
1   ben afflick xyz Address 5014123746
5   abc xyz New York    9999988888
4   nash    gordon  charlotte NC ADDRESS    111200110
3   christina   smith   test address    111000110

But i DO NOT want this , i don't want data to be inserted that is already present. I want only the newly added data to get inserted into the SQL table.

Can someone please help me to achieve this?

you can do a look up with the base table against the key attributes and insert only non matched records — ughai
Based on your sample data, the first column is a unique identifer. Is this always the case? — Nick.McDermaid

Neil P Neil P · Accepted Answer · 2015-04-17T08:12:11

Another method is to load your file into a staging table in the database and then use a merge statement to insert data into your destination table.

In practice this would look like a data flow from your flat file to your staging table and then a execute sql task containing a merge statement. You can then update any matching values as well, should you wish to.

merge into table_a 
using stage_a
on stage_a.key = table_a.key
when not matched then insert (a,b,c,d) values ( a,b,c,d )

how to avoid re-inserting data into sql table while re-running SSIS package that loads data from flat file to SQL table?

2 Answers