Use the following rules and guidelines when you configure the Lookup transformation to return multiple rows:
The Integration Service caches all rows from the lookup source for cached lookups.
You can configure an SQL override for a cached or uncached lookup that returns multiple rows. The override SQL must return data sorted on the lookup keys. If the data is not sorted on the keys, you might get unexpected results.
You cannot enable dynamic cache for a Lookup transformation that returns multiple rows.
You cannot return multiple rows from an unconnected Lookup transformation.
You can configure multiple Lookup transformations to share a named cache if the Lookup transformations have matching cache lookup on multiple match policies.
A Lookup transformation that returns multiple rows cannot share a cache with a Lookup transformation that returns one matching row for each input row.