SSIS 2012 - Insert new rows, ignore existing rows

问题:

It has been quite a long time since I've used SSIS. I'm using SSIS 2012. I'd like to insert rows from one table into another table. But, I'd like to only insert rows from the source, into the destination, that don't exist in the destination, based on the primary key. For existing rows, I'd like to simply ignore the rows from the source. I know it's a simple matter to insert rows from one table to another, but what's the best (most efficient) way to check to see if a row from the source exists in the destination and ignore it if it does?

回答1:

Between your source and destination component, add a Lookup Component.

Configure the Lookup to Redirect to No Match Output for unmatched rows.

In your lookup source, write a query like SELECT T.PK1, T.PK2 FROM dbo.MyTable T where you only pull back the primary keys and then compare you input data to the target table's.

What comes out of the no match output will be rows that do not exists in the destination.

Sample screenshots for a 2008 problem

Importing a large data file with sorts and joins in SSIS