Hi All,
Today I would like to tell you the required settings to be done for using Merge Join in SSIS.

Merging the data from 2 different sources can be implemented in various ways. And that depends on many things like both sources are on same SQL server or on different SQL servers but available as linked server or one source is MS SQL Server and other is Oracle etc.

But apart from all these, you concentrate on mainly 2 things, which will quickly get your job done.

We know basic requirements of using “Merge Join” data flow component.

1. Both sources should have data sorted.
2. Columns which will take part in joining condition should have same metadata.

Now, how to configure “Merge Join” task. Just follow the steps explained in following scenarios.

Scenario 1: If you are pulling data from some sources, and you have to do some transformations on the data then simply use “Sort” data flow components as displayed in below screen-shot.

Sort both inputs on required columns. If you have multiple columns in joining condition then make sure to use the same sort order in both sort components. And you are good to go for configuring Merge Join task.

Scenario 2: If you are pulling data from some sources, and right after that you have to use “Merge Join” task then, configure “OLE DB Source” as shown below:

Connect OLE DB Source output to Merge Join task.

Open Advance Editor of OLE DB Source.

Configure “IsSorted” property of OLE DB Source Output.

Configure columns which will take part in joining condition. Make sure to use the proper sort order by setting “SortKeyPosition” property of output column.

Important Note: “SortKeyPosition” always starts with 1. So if you have more than one column to be sorted then use 1 for first column and then 2 for second column. (Starting the “SortKeyPosition” will give you an Error)

That’s it. Now, you are good to go for configuring Merge Join task. I know configuring this wont be an issue if you have configured your input sources for Merge Join task properly as explained above.

Your Merge Join task is ready for use.

I hope this will be handy when you run across any issues configuring Merge Join task in SSIS.
Thanks, Khilit