How to validate a destination table with a source table data using SSIS?

Problem: How to validate a destination table with a source table data using SSIS?

Solution:   Merge Join Transformation

The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin.

You can configure the Merge Join transformation in the following ways:

·         Specify the join is a FULL, LEFT, or INNER join.

·         Specify the columns the join uses.

·         Specify whether the transformation handles null values as equal to other nulls.

Note: merge join needs sorted inputs.

Example:

In the example, I would like to show we can use a Merge join to insert rows into a destination table based on new entries in the source table.

Step 0: Create a new package and in the control flow drag a data flow task from tool box.

 Step 1: Drag and drop two Ole DB Sources and call one as source table and another as a destination table.  Next go to transformations and pull sort transformation to sort the source table and do the same for destination table source and in the sort transformation select the column you would like to join both source and destination.

Step 2: Use Merge Join transformation and connect both the tables with inner, left or full based on need and select columns to be joined and output.

Step 3: Conditional split helps to separate the new rows inside by identify the nulls when left join is specified.

Step 4: map to the destination table and execute  the package.

Output:

Sent only the new rows.