I got ahead of myself in a post entitled SSIS Snack: Configuring an SSIS 2005 Lookup Transformation for a Left Outer Join and my friend Eric Wisdahl (Blog) called my out in the comments.
A Good Test
If you've not been warned previously, I'm an engineer. I learn an awful lot by testing - I sometimes refer to this as my poke-it-and-watch-where-it-wiggles methodology.
To test this, I created and populated two tables in my AndyWorks database, TestSourceA and TestLookupA, using the following T-SQL:
Create Table TestSourceA
(ID int identity(1,1)
Create Table TestLookupA
insert into dbo.TestSourceA
insert into dbo.TestLookupA
Next, I tested a left join between the tables with this statement:
from dbo.TestSourceA a
left join dbo.TestLookupA b
on b.name = a.name
The results are:
In an SSIS Data Flow Task, I added an OLE DB Source Adapter and set it to read from AndyWorks using a SQL Statement:
Downstream, I connected a Lookup Transformation aimed at AndyWorks.dbo.TestLookupA. On the Columns tab the Lookup auto-mapped the name fields. I selected the value field from the Available Lookup Columns:
I clicked the Configure Error Output button and set the Lookup Error response to "Ignore failure":
I closed the Lookup Transformation editor and added a Trash Destination Adapter from SQLIS.com. I also added a Grid Data Viewer between the Lookup and Trash Destination, so when comepleted the Data Flow Task appeared as shown:
When I execute the package, the data viewer displays the results predicted in Eric's comment:
As Eric pointed out, the Lookup still only returns the first match in the table. There are results missing from the Left Join statement results shown earlier.
But the Lookup does not fail when there is no match - it simply returns NULL as the Left Join does. That was the point I was trying to make in the earlier post, but I didn't do a very good job. Thanks for pointing that out Eric!