Until 8.6 version lookup transformation is a passive transformation but from 9.x version we can configure it as active/passive transformation.
Creating Lookup Transformation
Creating Lookup Transformation
- Go to mapping designer->Mappings->Create mapping
- Go to Transformations->Select lookup Transformation-> Specify Name
- Now we get "Select Lookup Table" dialog box for selecting lookup source. From this dialog box we can choose active or passive option.
- We can choose one of the below option for lookup source definition.
- Source definition from the repository.
- Target definition from the repository.
- Source qualifier in the mapping (applicable only for non-reusable lookup transformation)
- Import a relational or flat file definition as the lookup source.
- In the dialog box we have an option "Return All values on multiple match". When we are creating passive lookup transformation do not check it. When we create active lookup transformation we can click on this option. Once we create active lookup can not change back to passive transformation.
- Click on OK or click skip if we want manually add ports.
- Edit->Go to lookup properties configure properties.
- Go to condition tab-> Add a condition.
- Import source EMP table, drag it to the mapping designer
- Create target by adding 2 columns DNAME, LOC to EMP table. Drag it to the mapping designer.
- Go to mappings-> Create->Select Lookup transformation
- Select Lookup table dialog box->select the required target(EMP_lookup)->OK
- Drag DEPTNO fro EMP to lookup transformation table.
- Edit lookup Transformation->Condition->Add condition->DEPTNO=DEPTNO=1 (Both data types should be same).
- Connect DNAME, LOC from Lookup to Target.
- Connect all other remaining ports from Source qualifier to Target.
