Thursday, February 16, 2012

Append new records from ODBC source

I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?

Steve Jensen wrote:

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

Steve,

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

|||

Sounds good. Could you give me some detailed instructions for doing this?

Thanks,

Steve

|||

Steve here is a detailed description:

http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

I Hope this can help you

Rafael Salas

|||

Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.

Thanks,

Steve

No comments:

Post a Comment