Floating FB popout byF5debug

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

 

This is part 50 of the series of article on SSIS

Introduction:

In this article we are going to see on how to use Merge transformation in SSIS Packaging. Merge transformation is used in cases where we need to get data from 2 different data source and merge in order specified and send the result to the destination. Merge transformation can be very effective when we need to do manipulation across the data sets or the data source.

[more]

Let’s jump start on how to use this task in real time and see the steps to do the configurations. 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 Transformation task. Once you open the project just drag and drop the Merge transformation control and a source provider as shown in the below image.

clip_image001

Now we need to start configuring the transformation. Let us start with configuring the source data, for that we need to create table as shown in the below screen.

clip_image002

Scripts:

Create table Merger1(   Empid int,EmpName Varchar(50))

Create table Merger2(   Empid int, EmpName Varchar(50))

Insert into Merger1 values (1,’Arun’)

Insert into Merger1 values (3,’Karthik’)

Insert into Merger1 values (4,’Amit’)

Insert into Merger1 values (6,’Vinoth’)

Insert into Merger2 values (2,’Vijay’)

Insert into Merger2 values (3,’Karthik’)

Insert into Merger2 values (5,’Ruthesh’)

Insert into Merger2 values (7,’Ruthra’)

Select * from Merger1

Select * from Merger2

To configure the sources we need to do the below steps. Here we are going to use a query to fetch the data as shown below

Select Empid,EmpName From Merger1 Order by Empid

Select Empid,EmpName From Merger2 Order by Empid

Your screen looks like below after you configure the first data source.

clip_image003

Similarly do the second data source as shown below

clip_image004

Now once configured the sources we need to configure the Merge transformation task by mapping both the merger source with the merge as shown below

clip_image005

Here we need to do some steps do take the Merge task accept the sources, for this we need to give the sorting to true. Since the task by itself will not make the sorting to merge the data. For that you need to right click on the sources task and select “Show advanced editor” and select the input output properties tab and click on the OLEDB Source output. Then change the IsSorted property to True on the output.

Now we are done with configuration of the sources and the merge task. Now we need to get the output for that we can use a flat file destination as shown in the below screen

clip_image006

Now execute the task (Press F5) you will get the desired output as shown below.

clip_image007

The output we can see in the file which we specified at the configuration of the destination task.

clip_image008

Conclusion:

So in this article we have seen on how to use the Merge 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

Comments (3) -

Ultra Sound Tech
Ultra Sound Tech
9/5/2013 2:43:08 AM #

By devidanderson : A how to tutorial about International cash transfers, global cash transfer, on-line income transfer, Finance with step by step guide from devidanderson. <a href="http://theultrasoundtechnician.com/diagnostic-medical-sonographers/">What Is The Average Salary Of An Ultrasound Technician</a>. I will take that advice. From any place of that corporation anyplace in the globe, the client can send cash to an further location of the very same company someplace else in the globe.

jg
jg
9/11/2013 10:11:14 PM #

nm

Rajesh Kumar
Rajesh Kumar
9/24/2013 7:24:01 AM #

I think we have to use Union all instead of Merger

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