SSIS Source and Destination in Sync

Question

SSIS Source and Destination in Sync

joel.ireland

SSC Veteran

Points: 234
More actions
July 13, 2010 at 12:49 pm

#387343

Hi all,
Im very new to SSIS however I have a problem which Im sure there is a easy answer.
I have a ODBC database connection as my source. This has 2 tables that I am interested in. The first table has approx 300,000 records and the second over a million.
I have setup a very simple SSIS package that connects to the source db via ODBC and creates a SQL table and inserts all records.
The issue I am having is that when I re-run the package obviously it creates duplicate records in the destination SQL DB. I need it to be able to do an UPDATE on the existing records and then INSERTS for any new records?
Is this possible?? and if so which Data Flow Transformation should I use? I have currently got it doing a truncate of the table first however would like to avoid having to do this every time if possible.
Thanks for your help
Cheers
Joel

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Tim Mitchell SSCoach Points: 15695 More actions · Answer 1

There are a couple of ways you could handle this. First, you could use native SSIS components - using the Lookup Transformation, you could look up the records in the target table, and then either insert them if they don't already exist, or update them if they do. This solution works fine, but know that it doesn't scale well with large sets of data. Your lookup and the downstream OleDB Command process data a row at a time, which can be expensive in terms of system resources.

Alternatively, you could use the T-SQL MERGE statement, which is new to 2008. This is essentially an upsert operation, performing either an insert or an update based on whether the record already exists in the target table. More info on the MERGE statement here: http://technet.microsoft.com/en-us/library/bb510625.aspx

I've used both methods described above. I lean toward the former when I know the number of records will remain small, but I like the MERGE statement when the solution needs to scale for many rows.

hth,

Tim

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

joel.ireland SSC Veteran Points: 234 More actions · Answer 2

Thanks for the advice.. Will give the MERGE statement a go.

Cheers again

Joel

Tim Mitchell SSCoach Points: 15695 More actions · Answer 3

Glad to help. I'd be interested to hear your thoughts after you try it.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices