This pattern is used when you have transformation logic which relies on a key which might not already exist. If the lookup fails to find the key, a new key is generated with a script task so it can be used later on downstream. Optionally, the key could be inserted immediately into the reference table following the script task (multicast to send to an OLEDB Destination).
The way you generate the key will vary depending on the situation. If you don't need to worry about concurrency issues, you could use an Execute SQL Task in the control flow to retrieve the next or current maximum key value, and store it in a variable. You’d then increment it each time you go through the key generation process.
PingBack from http://blog.a-foton.ru/index.php/2008/11/23/lookup-pattern-key-generation/
From the Lookup presentation I put together for the MS BI conference in October, here is a series of
The Cache Transform isn't currently able to update (i.e. append to) an existing persistent cache file.
The SQL Server Integration Services team added valuable new caching options (and scalability) to the