Joining Heterogeneous Sources

Joining Heterogeneous Sources can't be done using the Source Qualifier Transformation.

Source Qualifier transformation is used to join homogeneous sources.Whenever we are using a flat-file the Source Qualifier properties gets disabled and we cant use it to join the Sources.

This is one of the reason why we have Joiner transformation , Also when we have Sources which don't have a common column / primary-foreign key relationship , we have to go to joiner transformation.

Joiner T/f is used for following

1) Join data from different Relational Databases.

2) Join data from different Flat Files.

3) Join relational sources and flat files.


Sources which don't have common column can be joined by creating a dummy column using Sequence generator transformation.

Scenario 1

Suppose we have to load three targets in such a way like

1) the first target should be loaded with 1,4,7... records
2) the second target should  be loaded with 2,5,8... records
and
3) the third target should be loaded with 3,6,9 ... records

How can this be achieved?


Answer :

1) Lets have a expression transformation and load all the data from Source Qualifier to it.

2) Now we will create a Sequence generator transformation to generate the Sequence for identifying the records to be loaded for various targets.

3) Let the Connect the Sequence generator to the expression transformation

4) Lets use a router transformation to distribute the records to the three targets by creating three groups in it. Namely target1 , target2, target3

5) The following conditions are given on the three targets in router transformation to properly send the records to respective targets.
target1 --- mod(Sequence_no,2) = 1
target2 --- mod(Sequence_no,2) = 2
target3 --- mod(Sequence_no,2) = 0   

6) The Outputs of router is connected to respective targets.