Introduction
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
From Person.Contact
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.
:{> Andy
This still doesn’t act like a left outer join. More like a left outer join to a subquery for the (non-deterministic) top 1 of every distinct value.
The lookup component matches only the FIRST record that it finds. If there is more than one record that would match, it only takes the first one instead of outputting a record for every combination. If you need the true left outer join functionality you should instead use merge join.
Of course, I know you know this, but it would be helpful to explain to people reading the blog 🙂
Hi Eric,
You are correct and my language is misleading.
I need to do a followup post to adequately cover this topic.
Thanks for the comment, for the correction, and for reading my blog!
:{> Andy
Unless your table contains only one column you should choose the ‘Use the results of a SQL query’ option and explicitly state the columns that you want to match on and return. This gives SQL Server a chance to use an appropriate existing index as well as reducing the amount of data passed to the buffer.
Another trap worth mentioning (at least, I felt :)) is that, so as to circumvent the issue of failing LookUp component because of no-matching row, we configure the LookUp task to "Ignore Failure" which results in dumping the NULLs into the destination table for every non-matching row( in case we have added a subsequent Destination component to LookUp for capturing all those matched rows ).
Instead, one could set as "Redirect rows to no match output" in the LookUp Transformation Editor ( we don’t have to actually redirect to another destination… we can just set it ). By this only matched rows fall into the actual destination adapter.
— In ‘thoughts’…
Lonely Rogue.
PERO CUANDO LO EJECUTO POR SEGUNDA VEZ EN EL SISS EL LOOKUP SE GENERA ERROR POR LA LLAVE PRIMARIA…
Thank you