Lookup Pattern: Cascading
The cascading lookup pattern uses two lookup transforms with different cache modes. A common use of this pattern is when your data flow is inserting new rows into your reference table.
The first lookup in the chain is set to Full cache mode. Since it creates its cache before the data flow begins, it will only have the keys that exist before the package was executed.
A second lookup is hooked up to the No Match output of the first, using a Partial Cache mode. This one will pick up any rows that have been added since the data flow began. We hookup any logic needed to generate the key, or insert the row into the database into the No Match output of the second lookup.
Note, you don’t really need that first lookup – you could accomplish the same thing with a single lookup in a partial cache mode. But if you’re processing a good number of rows, and a large number of your keys already exist, the first lookup will improve your overall performance.
Make sure that you do not enable the Miss Cache ("Enable cache for rows with no matching entries" on the advanced options page). If you do, the partial cache won't go to the database the next time the key value comes in again.
