In SSIS 2005, the Lookup Transformation will fail if it does not find a matching record in the lookup table if configured with the default settings.
Build the Data Flow
Create a Data Flow Task and add an OLEDB Source. In the OLEDB Source Adapter, connect to AdventureWorks and use the following T-SQL statement to extract data from the AdventureWorks.Person.Contact:
Select FirstName, MiddleName, LastName
Configuring the Lookup
Drag a Lookup onto the Data Flow canvas and connect the output of the OLEDB Source Adapter to it. Open the editor and connect to a destination - any destination. For me, I used AndyWorks because I'm so vain.
I also clicked the New button beside the "Use a table or a view" dropdown and created a table in AndyWorks from inside SSIS (love that feature!).
Next I clicked the columns tab and the transformation auto-mapped the available Input Columns to the Available Lookup Columns. I checked all the columns in the Available Lookup Columns, then prefaced each Output Alias with "Dest_":
Designed to Fail
If I execute this task right now, it will fail:
The error is: [Correlate [xx]] Error: Row yielded no match during lookup. The name of the Lookup transformation is "Correlate".
The reason? The new table is empty, so there are no records that match the rows in the pipeline (from the OLEDB Source Adapter, which is reading the AdventureWorks.Person.Contact table). So how do we keep it from failing?
How to Keep the Lookup From Failing
Click the Configure Error Output button:
When the Configure Error Output window displays, change the Error response to the Lookup Output from Fail Component to Ignore Failure:
Now when you execute the Data Flow Task, it will succeed:
There are still no records in the destination table. The difference is how the Lookup Transformation responds to not finding a match in the destination for rows flowing from the OLEDB Source Adapter.