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 · Accepted 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

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

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

Examples related to sql-server-2008

Examples related to ssis