Router Transformation

The Router Transformation is an Active and Connected Transformation.

A Router transformation is similar to a Filter transformation because both transformations allow you to use a condition to test data. A Filter transformation tests data for one condition and drops the rows of data that do not meet the condition. However, a Router transformation tests data for one or more conditions and gives you the option to route rows of data that do not meet any of the conditions to a default output group.

If you need to test the same input data based on multiple conditions, use a Router transformation in a mapping instead of creating multiple Filter transformations to perform the same task.

The following figure shows a sample Router transformation:

1. Input ports.

2. Input group.

3. User-defined output groups.

4. Output ports

5. Default output group

Note:

The Router transformation is more efficient then Filter transformation. For example, to test data based on three conditions, you only need one Router transformation instead of three filter transformations to perform this task. Likewise, when you use a Router transformation in a mapping, the Integration Service processes the incoming data only once. When you use multiple Filter transformations in a mapping, the Integration Service processes the incoming data for each transformation.

A Router transformation consists of input and output groups, input and output ports, group filter conditions, and properties that you configure in the Designer.

Working with Groups

A Router transformation has the following types of groups:

• Input: The Group that gets the input ports
• Output: There are two types of output groups 1) User-Defined Groups and Default Group, You cannot modify or delete output ports or their properties.

User-Defined Groups

You create a user-defined group to test a condition based on incoming data. A user-defined group consists of output ports and a group filter condition. Create one user-defined group for each condition that you want to specify.

The Integration Service uses the condition to evaluate each row of incoming data. It tests the conditions of each user-defined group before processing the default group. The Integration Service determines the order of evaluation for each condition based on the order of the connected output groups.

If a row meets more than one group filter condition, the Integration Service passes this row multiple times.

The Default Group

The Designer creates the default group after you create one new user-defined group. The Designer does not allow you to edit or delete the default group. This group does not have a group filter condition associated with it. If all of the conditions evaluate to FALSE, the Integration Service passes the row to the default group. If you want the Integration Service to drop all rows in the default group, do not connect it to a transformation or a target in a mapping.

Connecting Router Transformations in a Mapping

• When you connect transformations to a Router transformation in a mapping, consider the following rules:
• You can connect one group to one transformation or target.
• You can connect one output port in a group to multiple transformations or targets.
• You can connect multiple output ports in one group to multiple transformations or targets.
• You cannot connect more than one group to one target or a single input group transformation.
• You can connect more than one group to a multiple input group transformation, except for Joiner transformations, when you connect each output group to a different input group. 

Note:

You cannot modify or delete output ports or their properties.

Sample Mapping:

1. Connect and Open the folder if not already opened.

2. Select Tools --> Mapping Designer

3. Select Mappings --> Create

It will pop-up "Mapping Name". Enter the mapping name of your choice" and Click on 'OK'. Example: m_emp_dept_router.

4. Drag the Source and Target definitions into workspace if they are already exist. If not click here to know how to create or import Tabledefinitions.

5. Select 'Transformation' from Menu --> Create

That will appear you 'Select the transformation type to create:'

a) Select 'Expression' from drop down and 'Enter a new name for this transformation:' as " rtr_emp_deptno"

b) Click 'Create' and 'Done'

or

a) Click on Router Transformation icon marked below in below snapshot.

b) Click in the workspace in Mapping Designer.

c) Select RTRTRANS in workspace and Right Click --> Edit.

d) In Transformation tab --> Click on 'Rename' highlighted above which will pop-up 'Rename Transformation'. Enter the Transformation Name: "rtr_emp_deptno"

e) Click on 'OK'

f) Click on 'Apply' and 'OK'.

6). Drag required ports from 'SQ_EMP' Source Qualifier to 'rtr_emp_deptno' Router Transformation.

7) Select 'rtr_emp_deptno' router Transformation and Right Click --> Edit --> Ports Tab.

a) Create two group by "Add a new Group to this router' as highlighted above snapshot.

b) Add a filter condition the Group 'Deptno_10' as DEPTNO=10.

Similarly Add a filter condition the Group 'Deptno_20' as DEPTNO=20.

 

c) It will create 3 Groups as above snapshot.

Note: 2 User Defined Group and 1 Default Group as show in below snapshot.

8) Copy the target instance 'EMP' to create other 2 target instance and name them as EMP_10, EMP_20 and EMP_30 as below.

 
9) Drag port from router group 'Deptno_10' to Target instance 'EMP_10', group 'Deptno_20' to Target instance 'EMP_20' and Default group to Target instance 'EMP_30' as above.

10) Select 'Mapping' from Menu --> Validate.

11) Select 'Repository' from Menu --> Save.