Update/Insert SQL Server 2005 Db With Data from Intersystems Database

• mia892

  Newbie

  Points: 3

  More actions

  November 25, 2009 at 7:22 am

  #214252

  I need to use SSIS to import data from an Intersystems database. The source data (Intersystems DB) is made of joined queries. The destination DB tables will be created based on the queries from the source. I need to be able to determine if a record is new or updated. If it is a new record, I need to insert it, otherwise I just need to update the record on the SQL 2005. I am new to SSIS, so I am not sure how to go about. Any suggestions or code samples?

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  November 25, 2009 at 9:34 am

  #1084166

  There are basically 2 methods suggested for doing this:

  1. Use a Lookup component and send the unmatched rows to the insert and the matched rows to an Execute SQL Task that does an update. The downside is that this is slow.

  2. Use a staging table. Basically your package will purge the staging table, reload it, and then have an Execute SQL Task that does an UPDATE where the row already exists and then an INSERT of the new rows in the staging table using a LEFT OUTER JOIN. Downside is that you need some extra space, upside is that it is faster because the UPDATE and INSERT operations are set-based.

  Feel free to post more questions.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question