Router transformation

Chapter 18 . Router Transformation

• Active and connected.(number of rows can increase in the output if same row satisfies multiple condition)
• 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.
• The Router transformation is more efficient. For example, to test data based on three conditions, you only need one Router transformation instead of three filter transformations to perform this task.
• Another advantage is that 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 has the following types of groups:
  ♦ Input: The Designer copies property information from the input ports of the input group to create a set of output ports for each output group.
  ♦ Output: There are two types of output groups:
  1) User-defined groups
  2) Default group

Note that we cannot modify or delete output ports or their properties.

• We 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 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 Integration Service processes user-defined groups that are connected to a transformation or a target in a mapping. The Integration Service only processes user-defined groups that are not connected in a mapping if the default group is connected to a transformation or a target.
• Defualt Group:

1. The Designer creates the default group after you create one new user-defined group.
2. 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.
3. If all of the conditions evaluate to FALSE, the Integration Service passes the row to the default group.
4. 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.
5. The Designer deletes the default group when you delete the last user-defined group from the list

• Using Group Filter Conditions:

1. You can test data based on one or more group filter conditions. You create group filter conditions on the Groups tab using the Expression Editor.
2. A group filter condition returns TRUE or FALSE for each row that passes through the transformation, depending on whether a row satisfies the specified condition. Zero (0) is the equivalent of FALSE, and any non-zero value is the equivalent of TRUE. The Integration Service passes the rows of data that evaluate to TRUE to each transformation or target that is associated with each user-defined group.
3. There is no group filter condition associated with the default group.

• Adding Groups:

Adding a group is similar to adding a port in other transformations. For every new group that we add the Designer copies property information

from the input ports to the output ports.

1. Click the Groups tab.

2. Click the Add button.

3. Enter a name for the new group in the Group Name section.

4. Click the Group Filter Condition field and open the Expression Editor.

5. Enter the group filter condition.

6. Click Validate to check the syntax of the condition.

7. Click OK.

• Ports:

Input ports are in the input group, and output ports are in the output groups.

-Input ports: Input ports can be created by copying them from another transformation or by manually creating them on the Ports tab.

-output ports: The Designer creates output ports by copying the following properties from the input ports:

♦ Port name

♦ Datatype

♦ Precision

♦ Scale

♦ Default value

When you make changes to the input ports, the Designer updates the output ports to reflect these changes. You cannot edit or delete output ports.

Names of the output port are dependent on the input port names.

Example:

• Rules for connecting Router transformation:

♦ 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.

• Steps to create Router transformation.

1. In the Mapping Designer, open a mapping.

2. Click Transformation > Create.

Select Router transformation, and enter the name of the new transformation. The naming convention for the Router transformation is RTR_ TransformationName. Click Create, and then click Done.

3. Select and drag all the ports from a transformation to add them to the Router transformation, or you can manually create input ports on the Ports tab.

4. Double-click the title bar of the Router transformation to edit transformation properties.

5. Click the Transformation tab and configure transformation properties.

6. Click the Properties tab and configure tracing levels.

7. Click the Groups tab, and then click the Add button to create a user-defined group.The Designer creates the default group when you create the first user-defined group.

8. Click the Group Filter Condition field to open the Expression Editor.

9. Enter a group filter condition.

10. Click Validate to check the syntax of the conditions you entered.

11. Click OK.

12. Connect group output ports to transformations or targets.