Sending Odd-numbered records to one target and even-numbered records to another target
OR
How to send the alternate record to the target?
OR
Odd Employee IDs move to the Odd_Employee table and Even Employee IDs move to the Even Employee table.
Source Table: OT_SRC_DEV1.Employees
Target Table OT_TGT_DEV1.Odd_Employees; -- for odd Records
Target Table OT_TGT_DEV1.Even_Employees; -- for Even Records
Step 1: Drag the Source on Source Analyzer
Step 2: Drag the Target two times on Target Designer ( Rename the target first target table as Even_Employees and second target table as Odd_Employees)
Step 3: Create target table with the help of Generate/Execute SQL Option from Informatica Target Designer
Step 4: Connect to Target Schema, Select Create Table Option (If practised table exists then Select Drop table) Click Generate and Execute to create the target table on Target Schema.
Step 5: Go to Mapping Designer Drag Source and Target Start Developing the business logic.
Step 6: We need the Ordered table with respect to employee ID to move the Even Employee Id in the even table and the Odd employee Id in the odd table, Select "Number of Sorted Ports as 1" in Source Qualifier Properties
Step 7: Add Expression Transformation and Sequence Generator Transformation connect as shown bellow
Step 8: Drag NextVal from Sequence Generator Transformation to Expression Transformation and crate an extra out port v_EvenOdd with IIF(NEXT_VALUE % 2=0,1,0)
The If Condition returns 0 if the condition is true for even number (Next_Value%2=0) else it returns 1 for odd number.
Step 9: Add Router Transformation transform the result to two targets and use following the logic in Router transformation. ie. add two Group Name with logic as shown bellow
Group Name : ODD
Group Filter Condition : IIF(v_EvenOdd=0,1,0)
Group Name: Even
Group Filter Condition :IIF(v_EvenOdd=1,1,0)
Step 10: Connect the Express Transformation and Router Transformation as follows also connect the Router Out port Transformation to target (Odd to Odd_Employee Table and Even to Even_Employee Table)
The Complete Design is as follows,
Finally, Create Workflow and Connect the Relation Source table Schema and Target Schema and run the workflow to get the final result.
Step 11: Select Source Relational DB in the Workflow manager
Step 12: Select Target Relation DB for both the target DBs. Select Target Load type as Normal, If you wish to Run the Workflow multiple time then select the Truncate Target table option
After running Workflow see the Statics and logs (for detail flow and for any errors ) in Workflow Monitor
The Final Result in
Target Table OT_TGT_DEV1.Odd_Employees; -- for odd Records
Target Table OT_TGT_DEV1.Even_Employees; -- for Even Records
0 Comments