MERGE TRANSFORMATION
Using Merge
Transformation we can combine two sorted data-set into single data-set
basically, Merge Transformation used to combines rows from two sorted data flows
into one sorted data flow.
Following tasks
you may perform using Merge Transformation:
1.
Suppose
we have a scenario like, we need to merge data from a database table and excel
means we want to merge data from two different data sources. For such type of
scenario, you can use Merge Transformation.
2.
If
we want to merge data from two same structured tables but exist two different
servers.
3.
Sometimes
we get an error due to data in a row, after correcting errors in the data we
can re-merge rows easily.
See below
explanations may help you to understand Merge Transformation:
I do evaluate
here, you already know about the data source, data conversion, data flow, task flow, control flow, etc.
Note: Before Merge transformation, we need to sort
the data using Sort Transformation.
After sorting
data add data path to Merge Transformation, a dialog box will be open See
below:
Now choose one option from Input,
suppose we chose Merge Input 1 the same as we add data path from the second source.
Note: If Merge input 1 and Merge input 2 the column name is the same then no issue otherwise we have to map column, see below.
If we don’t want
to add data from the input you can ignore here, means we don’t want to add cAddress
column from Merge Input 2 so we will select <ignore> same you can do with
Merge Input 1.
See below full
data flow of merge transformation.
MERGE JOIN TRANSFORMATION
Merge join
transformation is the popular tool which is used by most BI developers, The
Merge Join Combine to sorted data into one output using the FULL, LEFT or INNER
JOIN.
You can configure
the Merge Join transformation in the following ways:
1. Specify the join is a FULL, LEFT, or INNER
join.
2. Specify the columns the join uses.
3. Specify whether the transformation handles
null values as equal to other nulls.
For example, you can
use a LEFT join to join a table that includes customer information with a table
that lists the phone of the customer. The result is a table that lists all customers
and their phone numbers.
The
implementation is the same as Merge transformation except the need to select Join
Type in Merge join transformation editor dialog box, see below:
Here we need to
select option of Join Type and (Full, Left or Inner join), suppose we select
option inner join then select map the column from two shorted data sources (in
above pic. cId mapped to cust_Id because of cId Primary key in customers table and
foreign key in custPhone table ).
To see the
result, we can enable data viewer after sorting and Merge Join transformation.
Result will be
look like below: Customers data set after sorting.
Customer- phone data set after
sorting.
Merge join transformation output.