Lookup Pattern: Key Generation

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.