Connected Lookup Transformation

1. Let us assume that EMP will be source table. DEPT will be LOOKUP table.
2. Create a target table T_CONN_LKP_EMP in target designer. Table should contain all ports of EMP table plus DNAME and LOC.
3. Creating Mapping by click Tools -> Mapping Designer.
4. Click Mapping-> Create-> Give name. Ex: m_CONN_LKP_EMP
5. Drag EMP and Target table.
6. Connect all fields from SQ_EMP (Source Qualifier transformation) to target except DNAME and LOC.
7. Then go to Transformation-> Create -> Select LOOKUP from list. Give name and click Create.

8. As DEPT is the Source definition, click Source and then Select DEPT.
9. Click Ok.
10. Now Pass DEPTNO from SQ_EMP to this Lookup. DEPTNO from SQ_EMP will be named as DEPTNO1. Edit Lookup and rename it to IN_DEPTNO in ports tab.
11. Now go to CONDITION tab and add CONDITION.
12. DEPTNO = IN_DEPTNO and Click Apply and then OK.
13. We are not passing IN_DEPTNO and DEPTNO to any other transformation from LOOKUP; we can edit the lookup transformation and remove the OUTPUT check from them.
14. The final mapping look like as shown below..

15. Then Mapping -> Validate and save the mapping.
16. Create Session and Workflow. Run the workflow and see the data in target table.
17. Make sure to give connection information for all tables.
18. Make sure to give connection for LOOKUP Table also.
19. We use Connected Lookup when we need to return more than one column from Lookup table.