SSIS: Handling lookup misses

posted by  jhorra (5)  12 months ago (blogs.msdn.com)

It's typical in data warehouse loads to have lookups that go against dimension tables. If the key doesn't exist, either a new one is created, or a default value is used.

There're two ways to do this:

1. Lookup is configured to redirect rows that have no match in the reference table to a separate output (error output), then use a derived column to specify a default value, and finally merge both the lookup success output and the output of the derived column using a union all transform.

2. Lookup is configured to ignore lookup failures and pass the row out with nul