Conditional Lookup & Returning Undefined Values on Error

Hi,

I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables.

I do lookuping like this

first a lookup that lookup my table with connected input column parameter

second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table

third a union that union lookup and derived column

i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step.

thanks

This link discusses two different ways to address this problem: http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

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 null values for reference data. A derived column downstream of the lookup then checks for null reference data using 'ISNULL' and replaces the value with a default value.

The first way is what you're doing now; the second way is what you probably want to do.

With that said, making upstream changes in a data flow is almost always going to require tweaking to downstream tasks. That's just the nature of how SSIS relies on metadata...