Update Rows in SSIS OLEDB Destination

Question

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

User · Answer

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:

Drag OLEDB Command [instead of oledb destination]
Go to properties window
Under Custom properties select SQLCOMMAND and insert update command ex:

UPDATE table1 SET col1 = ?, col2 = ? where id = ?
map columns in exact order from source to output as in update command

User · Answer

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

User · Answer

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

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

Examples related to sql-server-2008

Examples related to ssis