Merge Join in SSIS is used to join two sorted source of data together to form one result set
The following is an example:-
I am using two tables one is Post and another one is PostTypes from StackOverflow2010 DB
Here in this example, we are going to do inner join both tables on the basis of the PostTypeid of Post and id of PostType
To get a result set i.e. a table stating the type of post is being posted by user
Step 1:Open a new SSIS project take a DataFlow Task in Control Flow of the package
Now go to the DataFlow tab and add two OLE DB Source
Step 2: Configure the OLE DB Source Editor Click new and Give your Server Name and Database Name that you want to connect
Step 3:Select Data access mode as Table or View
Select the table from the dropdown here I selected Post in the POST OLEDB Source
Check the Columns tab in the left and select all the columns
Step 4:Same for POST TYPE Source
Select the table PostType
Check the Column tab and select all columns
Step 5: Add Sort function below the source the data from both the sources need to sort for applying merge operation
Step 6: Select PostTypeId column for sorting in the Post source sort and let all other rows Pass through
Check the Checkbox, at last, to remove duplicate rows and Click OK
We need to sort the table that is coming from the source so that the data is in the for both
Step 7: Select the Id column from PostType for sorting and let Type column pass-through
Check the Checkbox, at last, to remove duplicate rows and Click OK
Step 8: Add Merge Join Component and connect both the sort
A dialog box will ask for input Select Merge Join Left Input for the Post Source and Merge Join Right Input for PostType
Step 9: Select Join type Inner join and Check PostTypeId from left as Join key and Id as Join key from right
Uncheck the Id column at right
Step 10: Add OLEDB destination and connect it to merge join
Step 11: Configure OLE DB destination select the Data access mode as Table or view
Step 12:Click on New that is next to Name of the table a dialog box will pop up as shown below here the Table is renamed to Postypedt
Check the column mappings if the columns are mapped and Click Ok
Step 13:Execute the package
To check if the data is merged run the following query in SQL
SELECT [Id]
,[AcceptedAnswerId]
,[AnswerCount]
,[Body]
,[ClosedDate]
,[CommentCount]
,[ViewCount]
,[Type]
FROM [StackOverflow2010].[dbo].[Postypedt]
If you are new to MSBI start with the following must-watch video: -
