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:
And you’re done.
Here is how your operation should look on the end:
Good luck on you next task!