CODEWITHSUNDEEP
sql-serverssis
LOL
LOL

Reputation: 111

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

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?

Upvotes: 0

Views: 1786

Answers (2)

Arun Gairola
Arun Gairola

Reputation: 884

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. 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 only. lookup Transformation link ---

http://www.codeproject.com/Tips/574437/Term-Lookup-Transformation-in-SSIS

sort and lookup transformation in ssis

Upvotes: 1

Neil P
Neil P

Reputation: 3190

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 )

Upvotes: 1

Related Questions