When creating the lookup transformation itself it’s a must to specify whether or not the lookup transformation returns a number of rows or not. Once you make the lookup transformation as energetic transformation, you can not change it again to passive transformation. The “Lookup Policy on Multiple Match” property worth will change into “Use All Values”. This property turns into read-only and you can not change this property.
As an instance, for every nation you possibly can configure the lookup transformation to return all of the states in that nation. You can cache the lookup desk to enhance efficiency. If you configure the lookup transformation for caching, the combination service caches all of the rows type the lookup supply. The integration service caches all rows for a lookup key by the important thing index.
Guidelines for Returning Multiple Rows:
Follow the beneath tips while you configure the lookup transformation to return a number of rows:
- You can cache all of the rows from the lookup supply for cached lookups.
- You can customise the SQL Override for each cached and uncache lookup that return a number of rows.
- You can’t use dynamic cache for Lookup transformation that returns a number of rows.
- You can’t return a number of rows from an unconnected Lookup transformation.
- You can configure a number of Lookup transformations to share a named cache if the Lookup transformations have matching caching lookup on a number of match insurance policies.
- Lookup transformation that returns a number of rows can’t share a cache with a Lookup transformation that returns one matching row for every enter row.