How to do Step by Step Merge Join Transformations – #123

 

In last post tip #122, We discussed Merge transformation. Now in this tip, we are going to discuss Merge Join. Although, it might be confusing sometimes Merge & Merge Join. So, let me explain it here.

As explain earlier merge is like a union operation in which it just collect all the records from provided sources without any condition. suppose if we have Person & PersonPhones two sources as shown below

Below is Person table with two columns personId & Name

PersonId Name
1 Ram
2 Shyam
3 Ghanshyam

Below is PersonPhones table with two columns PersonId & Phone

PersonId Phone
1 9999
2 3122
3 3422

Now if we talk about merge transformation then the result would be as shown below

Merge Transformation output

PersonId Name Phone
1 Ram  
2 Shyam  
3 Ghanshyam  
1   9999
2   3122
3   3422

Now, If we talk about Merge Join transformation for above same inputs (Person, PersonPhones) then we will get following output

PersonId Name Phone
1 Ram 9999
2 Shyam 3122
3 GhanShyam 3422

So, Merge Join is basically a join like Inner join, Left outer join, Full outer join etc.

I hope you got the context. Here we go step by step to implement the Merge join transformation

Step 1:-  Add the different sources which you want. For this example I am taking two sources which have AdventureWorks database and one source have Person table & another one have PersonPhone as shown above in the example.

We added data flow task as shown in below image and renamed it to Merge join data flow task.

DataflowTaskStep1

Now, in this data flow task, we will add data sources, in the first source we use person table and in the second source we use PersonPhone.

from person table, we are choosing BusinessEntityId, FirstName,MiddleName, LastName,suffix as shown in below figure

MergeSource1Step2 

In the other source, we are using BusinessEntityId & Phone number columns.

MergeSource2Step3

If you find any issue in above steps then please, follow previous articles of step by step SSIS on Indiandotnet.

Step 2:- Now once the source is configured, we are going to drag drop merge control as shown in below figure.

DragDropMergejoinStep4

Step 3:- Now to configure Merge join, we are going to drag drop inputs from the sources. When you try this at your end then you will get the following screen. Here you have to set left input and right input for the merge join.

ConfigureMergeJoin4

Now, When you tried it you might found following warning. Which means the provided inputs of datasource1 & datasource2 are not sorted.

WarningforSortStep5

Step 4:- Here you have the option to add a Sort transformation and then provide input to merge join transformation control which is fairly simple and we have discussed in previous tips. Here, I am sharing one more option.

In this, you have to right click on DataSource and choose option Advance Editor by which you will get the following screen. You have to select “OLEDB Source output” of “Input and output Properties” tab.

Here, we need to make the IsSorted property to True as highlighted in below snap.

IsSortedProblem6

Step 5:- Once the above Step is done next step is click on output column’s BusinessEntityId value. Here you have to set the SortKeyPosition to 1.

As highlighted in below screen.

BusinessEntitySortKEy

Step 6:- Once you configured sorted input sources then next step is to configure the merge join. So when you click on Edit option of merge join. You will get the following screen.

Here if you see, we can configure join type like Inner join, Left outer join, Full outer join.

For current example, we are choosing the option “Inner join”  and joining key is BusinessEntityId which is primary key in Person table and foreign key in PersonPhone table.

ConfigureMergejoinTypesStep6

Once the configuration is done. We can select what are the columns which we need as an output.

Step 7:- Now, Next step is to configure the output in excel. for this, we drag & drop a destination source. In the current example, we are using Excel as a destination. We are mapping the columns of output to excel as shown in below figures.

ConfigureExcelDestination

MappingInexecel

Step 8:- Once, everything is configured. We run the package and if everything is working fine then we will get the following screen.

MergeJoinRunResult

To cross check the result we open the excel and we get the output which we expected as shown in below figure.

ResultOutputinExcel

I hope this article might help you to understand the Merge join.

Enjoy !!!

RJ!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s