Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 52 – Merge Join Transformation in SSIS

 

This is part 52 of the series of article on SSIS

Introduction:

In this article we are going to see on how to use the Merge Join Transformation in SSIS packaging. Merge Join Transformation is similar to merge transformation is it handles multiple data from different source and merges into one process and handles it as if it came from a single source. Also Merge simply recombines portions of the data flow without changing the shape of the buffer, much like a UNION but which maintains the sort order.  Merge Join add elements to the buffer which will change its shape, similar to an INNER, LEFT OR RIGHT OUTER JOIN.

[more]

Let’s jump start to see this sample on how to set the properties of the sorting. You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

 

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on how to use the Merge Join Transformation task. Once you open the project just drag and drop the Merge Join transformation control and a source provider as shown in the below image

clip_image001

Here I configured the source data source from 2 different files as shown below

Source 1:

clip_image002

Source 2:

clip_image003

Now I created a table in the database and host it as the destination database as shown below

clip_image004

Script:

Create table ProductMaster

(

Productid int,

Productname varchar(50),

ProductDate datetime,

ProductLineId int,

ProductOrderID int,

Productorderdetail varchar(40)

)

Select * from ProductMaster

Now the source files are configured and the sorting properties are enabled as shown in my previous article. Now let us configure the Merge join task as shown below

clip_image005

Here I make an inner join to perform the task and I select productid as the primary key and select the list of columns that the destination table should have to be updated as shown in the above image. Now the task is configured and ready to have data. Now we need to have a destination system to update the data so we will have the OLEDB destination provider as shown in the below screen and we configure it with the exact mapping.

clip_image006

Now the complete package is ready to build and execute. Press F5 to do the process, you will see the screen as below

clip_image007

Now let’s see how it got affected in the table. Run the destination system query and see the end result which will be like below

clip_image008

Conclusion:

So in this article we have seen on how to use the Merge Join transformation task and the key configurations used in order to use this task handy.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter