Tutorial :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 ?


Solution:1

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


Solution:2

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


Solution:3

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.


Solution: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)


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »