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: SSIS 2005 Lookup Transformations 101

Introduction

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:

Use AndyWorks
go 

Create Table TestSourceA
(ID int identity(1,1)
,name varchar(50))

Create Table TestLookupA
(ID int
,name varchar(50)
,value int)

insert into dbo.TestSourceA
(name)
select 'a'
union all
select 'b'
union all
select 'c'
union all
select 'd'

insert into dbo.TestLookupA
(name, value)
select 'a',11
union all
select 'a',22
union all
select 'b',33
union all
select 'b',44

Next, I tested a left join between the tables with this statement: 

select
a.name, b.value
from dbo.TestSourceA a
left join dbo.TestLookupA b
 on b.name = a.name

The results are:

In SSIS

In an SSIS Data Flow Task, I added an OLE DB Source Adapter and set it to read from AndyWorks using a SQL Statement:

Select name
From dbo.TestSourceA

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!

:{> Andy

Published Tuesday, February 16, 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

 

Jonathan Amen said:

Thanks for this bit. The way the outbound success link reads it won't pass records with no match even if 'Ignore Failure' is selected. Thanks for saving me the testing time to figure this out on my own. /Thumbs up!

October 12, 2010 4:05 PM
 

Mohamed Harris said:

Good post

September 29, 2011 4:52 AM
 

Kamakshi Suram said:

Its really useful. Merge Join will take Sorted input and performs Left/swapleft(right)/full/inner joins where as Lookup will do left join.

June 15, 2012 5:30 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