Merge Transformation

The Merge transformation combines two sorted datasets into a single dataset. The rows from each dataset are inserted into the output based on values in their key columns.

By including the Merge transformation in a data flow, you can perform the following tasks:

· Merge data from two data sources, such as tables and files.

· Create complex datasets by nesting Merge transformations.

· Remerge rows after correcting errors in the data.

The Merge transformation is similar to the Union All transformations. Use the Union All transformation instead of the Merge transformation in the following situations:

· The transformation inputs are not sorted.

· The combined output does not need to be sorted.

· The transformation has more than two inputs.

Step 1:

Click Windows button and go to SQL Server 2008 R2 and run as a administrator.

It shows a bellow window.

Step 2:

Go to a views and create a new project (Short cut of New Project is Ctrl + Shift + N).

Step 3:

Give a project name (e.g. IS) and click OK button and then shows a bellow window.

Step 4:

To drag and drop the Data Flow Task

Step 5:

To edit the Data Flow Task then it open a Data Flow

Stem 6:

To dag and drop the OLEDB source and edit it.

Step 7:

To take OLEDB connection and select the table from given data base and select which columns are required.

Step 8:

Double click on the OLE DB data source to open a new window where we can set the properties of the connection.

Select the connection manager and click on new button to set the connection string to the TWO tables as shown below.

Step 9:

Set the connections to the TWO tables created using TWO OLE DB data sources.

We need to SORT the data before giving as input to the MERGE transformation and hence drag and drop TWO SORT transformations into Data Flow pane and provide output of each OLE DB source to each Sort transformation as shown below.

Step 10:

Take a merge transformation and give a inputs from two sorted inputs

Step 11:

Edit merge transformation and select which column merge.

Step 12:

The Input data from both the sources is SORTED now and hence add MERGE transformation to the pane and provide OUTPUT of both sort transformations as input to MERGE transformation as shown below