How to check if records from two tables match in SSIS?

Lookup transformation

In some cases we are not able to do comparison if the data we want to insert in a table already exist in the table or no using SQL queries or stored procedures.
In that case SSIS offer really nice task, Lookup transformation, that is easy configurable and easy to use.

Lets assume I have Person table and Employee table.
I want to compare if records from Person table exist in Employee table.

For that purpose I create Data source that points to the Person table.
I connect the data source with Lookup transformation.

In the Lookup transformation under connections you need to set up the destination we want to compare with, in this case the Employee table.

Next we need to do is map the columns under which we want to do the comparison:
My selection here is the primary keys of the tables (ContactID ans EmployeeKey):

After this, create new Data Destination that will point to your destination and connect the Lookup transformation predicate with the Data Destination.
When you connect the transformation you will be asked if you want the matching rows or the rows that don’t match:

In case you want to insert the rows that don’t exist in the destination table you should choose Lookup No Match Output.
In that case you need to make small modification in the Lookup transformation.
Under General tab in the “Specify how to handle rows with no matching entries”, choose “Redirect rows to no  match output” – the last selection.

And you’re done.

Here is how your operation should look on the end:

Good luck on you next task!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.