June 12, 2013 at 11:07 am
I am new to SSIS and I have been tasked to do use SSIS to import data from a file to our database.
The file is delimited and has 46 columns. The file can contain 1,000,000 records. The test file I have contains 700,000.
Each record in the file contains an ID. I need to take that ID, go to the database and see if the record already exists in the database. If it exists I need to update the record in the database. If it doesn't exist add the new record to the database.
So far I set up a Flat File Source and I can see all of my columns and data. I'm not sure where to go from here.
June 12, 2013 at 2:44 pm
The easiest way is to use ssis to import the data into a table, then execute a sql task that does the update / insert (merge).
Alternatively you could include a look up task in the data flow using the id column, if it finds a match, insert into a work table, if it doesn't find a match, insert into your main table. Then you need to perform an update from the work table. If you expect to have relatively few updates, this saves writing most of the data twice. If you do this, you have to configure the look up task to redirect failed look up rows to the "no match output". By default it wants to fail or do nothing with the rows that do not match.
I don't know of a fast way to make ssis perform the update.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply