Using Merge Join in SSIS to merge two similar tables

Question

I have two tables on two different servers that have identical schemas. For simplicity, let's say each table has 3 fields. I am trying to create a SSIS package that takes the data from both tables and merges it into one recordset.

I added two OLE DB sources, which get the same three fields from the two tables. I then have a Sort transformation on each, that then flows into a Merge Join. I set the join type to "Full Outer Join" on the Merge Join. I can select all six of the fields and see the output using a Data Viewer coming out of the Merge Join.

Let's say there were 25 records in each source table. In the Data Viewer, I end up getting 50 records - 25 with NULL in the last three fields, and 25 with NULL in the first three fields. I would like the output to be 50 records with data in only three fields. What I am doing wrong here? Should I be using some other sort of merge option?

I would greatly appreciate any suggestions on how to resolve what should be a simple task. Thanks!

Answer 1

The output from the merge join you are using is correct since you're using a full outer join. To fix your problem, use a merge transformation instead of merge join. This will combine your two sorted data flows into one sorted data flow. You've already set up your data flow correctly from your description (it should look like this):

Documentation can be found here: https://msdn.microsoft.com/en-us/library/ms141703.aspx