We can only return one port from the Unconnected Lookup transformation. As the Unconnected lookup is called from another transformation, we cannot return multiple columns using Unconnected Lookup transformation.
However, there is a trick. We can use the SQL override and concatenate the multiple columns, those we need to return. When we can the lookup from another transformation, we need to separate the columns again using substring.
As a scenario, we are taking one source, containing the Customer_id and Order_id columns.
Source:
We need to look up the Customer_master table, which holds the Customer information, like Name, Phone etc.
The target should look like this:
Let’s have a look at the Unconnected Lookup.
The SQL Override, with concatenated port/column:
Entire mapping will look like this.
We are calling the unconnected lookup from one expression transformation.
Below is the screen shot of the expression transformation.
After execution of the above mapping, below is the target, that is populated.
