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 an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

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

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2010/02/09/ssis-snack-configuring-an-ssis-2005-lookup-transformation-for-a-left-outer-join/

Published Tuesday, February 9, 2010 8:00 AM by andyleonard

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

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement