update the destination table

Question

I want to pull data from an source destination. How can I insert rows that are not already in the table and update rows that already exist ?

Answer 1

Use a slowly changing dimension transform see http://msdn.microsoft.com/en-us/library/ms141715.aspx

Answer 2

I would recommend CozyRoc's TableDifference component. I have used the predecessor from SQLBI.EU and it's very good.

I also recommend that instead of using a Command compponent to run individual updates on the stream with updates detected, that you stream the updates to a table and then use a single UPDATE statement in a SQL task to perform the update.

Answer 3

Hi

We could use LOOK UP on target for the existing recrods. On matching Update otherwise insert in the target.

Other approach of using the MERGE statement.

thanks

prav

Answer 4

I found this webcast very helpful in learning some different methods of doing "upserts" with SSIS. You can download the samples referenced in the webcast and see working examples of exactly what you need. MSDN Architecture Webcast: Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse (Level 200)