Home > SSIS > Merge two similar datasets into a dataset

Merge two similar datasets into a dataset


In my previous post, I have discussed about Union All transformation task. In short about the topic is merging multiple datasets into a dataset.

Today’s topic is very similar to that task. But, it is specially designed to merge only two datasets to populate single complete dataset. In the data flow design we may want to perform various operation like error data cleaning and other transformations. But, we need to merge the data at some point to perform another data transformation with a single dataset during the data flow. This task is helpful for the developer to create a complex dataset transformation.

Merge Transformation

Merge Transformation task merges two datasets into single dataset. We must sort the input dataset of this task. We can sort the dataset using Sort Transformation or can change the property  issorted to true in the merge task if the data is sorted already.

The metadata of both the inputs must be same. For instance, assume that we have a column name amount datatype currency in the first input then the column of the second input must have the same data type.

If the input column names are same in both the dataset then merge transformation task map the columns automatically else we can manually map the column using merge transformation editor.

For more information about this task read MSDN article.

Implementation 

Step 1: Create a package and add a data flow task.

Step 2: Go to Data flow design interface and add two excel data source. Ref: Excel files I am using are the same as in the union all transformation implementation.

Step 3: Add two sort task and connect each excel source and mark employee name as the key column for sorting.

Step 4: Add Merge transformation task and  open merge transformation editor.

Leaves in hours is an additional column in the second dataset. So, we have to manually map this column. By the way, if the column name are not similar then we have to map the columns manually.

Step 5: Click OK and add union all task at the end of the dataflow to view the dataset.

Step 6: Execute the package.

 

Thanks for reading. Please share your comments for this post in this blog.

 

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: