Update Rows in SSIS OLEDB Destination

30
votes

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

Data Flow Task

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.

3
sql-server-2008ssis

3 Answers

41
votes

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

http://www.sqlservercentral.com/Images/11369.png

Set based updates

enter image description here

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.

21
votes

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

2
votes

Well, found a solution to my problem; Updating all rows using a SQL query and a SQL Task in SSIS Like Below. May help others if they face same challenge in future.

update Original 
set Original.Vaal= t.vaal 
from Original join (select * from staging1  union   select * from staging2) t 
on Original.id=t.id