THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is CSO of Linchpin People and SQLPeople, an SSIS Trainer, Consultant, and developer; a Business Intelligence Markup Language (Biml) developer; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server 2012 Integration Services Design Patterns. His background includes web application architecture and development, VB, and ASP. Andy loves the SQL Server Community!
Note: Comments are moderated. Spam shall not pass! </GandalfVoice>

SSIS Snack: Configuring an SSIS 2005 Lookup Transformation for a Left Outer Join

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

 

Published Tuesday, February 09, 2010 8:00 AM by andyleonard
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Eric Wisdahl said:

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 :-)

February 9, 2010 8:41 AM
 

andyleonard said:

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

February 14, 2010 9:03 PM
 

Lempster said:

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.

February 18, 2010 4:14 AM
 

Lonely Rogue said:

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.

January 5, 2012 12:25 AM
 

CLAUDIA GARCIA said:

PERO CUANDO LO EJECUTO POR SEGUNDA VEZ EN EL SISS EL LOOKUP SE GENERA ERROR POR LA LLAVE PRIMARIA...

October 11, 2012 4:15 PM
 

chidella said:

Thank you

January 30, 2014 5:26 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

My Company


Other Blog

Check out my personal blog...
http://andyleonard.me

Contact Me

Twitter: @AndyLeonard
Email: andy.leonard@gmail.com

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement