[sql-server-2008] Update Rows in SSIS OLEDB Destination

I have a data flow process where I have an OLEDB Source and an OLEDB Destination like below:

Source merges data from two staging tables and returns a result set (say, 50K rows). These 50K rows are present in the destination table as well but are old data.

SELECT * FROM staging1
UNION
SELECT * FROM staging2 

Generally, in the OLEDB destination we insert the returned dataset from the source to destination table, but in my case I have to update the old 50K rows with these new 50K rows.

Kind of a bulk update.

Can anyone please let me know how I can do that? I appreciate your help.

Use Lookupstage to decide whether to insert or update. Check this link for more info - http://beingoyen.blogspot.com/2010/03/ssis-how-to-update-instead-of-insert.html

Steps to do update:

1. Drag OLEDB Command [instead of oledb destination]
2. Go to properties window

3. Under Custom properties select SQLCOMMAND and insert update command ex:

   UPDATE table1 SET col1 = ?, col2 = ? where id = ?

4. map columns in exact order from source to output as in update command

You can't do a bulk-update in SSIS within a dataflow task with the OOB components.

The general pattern is to identify your inserts, updates and deletes and push the updates and deletes to a staging table(s) and after the Dataflow Task, use a set-based update or delete in an Execute SQL Task. Look at Andy Leonard's Stairway to Integration Services series. Scroll about 3/4 the way down the article to "Set-Based Updates" to see the pattern.

Stage data

Set based updates

You'll get much better performance with a pattern like this versus using the OLE DB Command transformation for anything but trivial amounts of data.

If you are into third party tools, I believe CozyRoc and I know PragmaticWorks have a merge destination component.