SSIS equivalent to Informatica''s Update Strategy

• Bob Harford

  SSC Enthusiast

  Points: 179

  More actions

  September 12, 2007 at 6:06 pm

  #120480

  I'm new to SSIS and am trying to figure out how to update a row.  I've read a lot of logic regarding lookup transformations, merge joins, etc.  No problem and it generally makes sense.  However, the bloggers that I've read leave out the final  key piece, how do you actually update the row in your data source after you've identified what needs to be updated and what needs to be inserted.  E.g., what precisely is the last step?

  I also  came across this:  "Unfortunately there is as yet no equivalent of Informatica's Update Strategy transformation" -- http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=148&messageid=201490

  So is that still true?  The last piece involves hand jamming a SQL statement or piping the update rows over to a temp table and, "...and then do a set-based update using the Execute SQL Task."?

  Thanks.

• greencg

  SSC Journeyman

  Points: 87

  More actions

  September 13, 2007 at 1:07 pm

  #733599

  Bob,

  Thank you for asking this question...

  I am looking for the same answer.  If I find anything out I sure will post here...

  For SSIS experts,

  My Scenario

  Have a DB2 database table with ProductID and Price.  I have SQL 2005 DB that with the same ProductID - The prices are updated daily on the DB2 system.  I figured I could use SSIS to update the prices on the SQL 2005 DB.  What would be the best solution.

  Thanks,

  Chris

   

• Dave Klug

  SSCertifiable

  Points: 5811

  More actions

  September 14, 2007 at 3:57 am

  #733698

  Loading to a staging table for the updates and then doing a set based update is the best option for performance.  If your result set is small, you could use the OLE DB Command but be aware this fires the inserts row-by-row.

  Dave

• Michael Earl-395764

  SSC Guru

  Points: 53873

  More actions

  September 14, 2007 at 5:28 am

  #733712

  I have had a great deal of luck using the SCD wizard to both find and update records.  It is very data type sensitive, but it does a good job of finding records by a key that have a difference in one or more fields and then updating them.

  If you have already found records you want to update, the OLEDB destination will not only let you insert, but it can be configured to update as well.