Lookup Transformation in SSIS

Question

Lookup Transformation in SSIS

wweraw25

SSC Eights!

Points: 953
More actions
January 15, 2018 at 7:14 am

#341657

IN OLEDB Source I've something like
Select ContactID, Name
FROM dbo.TableA
IN Lookup Transformation I need to pull only those ContactID's
I'm writing something like this
Select * FROM dbo.TableB
where ContactID = ? (How do I pass values of ContactID)
In SQL Query will look like
Select * FROM dbo.TableB where ContactID IN (Select COntactID FROM dbo.TableA)
How can I achive the same through lookup transformation??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

wweraw25 - Monday, January 15, 2018 7:14 AM
IN OLEDB Source I've something like
Select ContactID, Name
FROM dbo.TableA
IN Lookup Transformation I need to pull only those ContactID's
I'm writing something like this
Select * FROM dbo.TableB
where ContactID = ? (How do I pass values of ContactID)
In SQL Query will look like
Select * FROM dbo.TableB where ContactID IN (Select COntactID FROM dbo.TableA)
How can I achive the same through lookup transformation??

Easy enough.

Full cache mode
Specify how to handle rows with no matching entries: set to 'Redirect rows to nomatch output')
Connection: Use results of an SQL Query (Select ContactID FROM dbo.TableA)
Columns: Create join from dbo.TableB.ContactId to ContactId in 'Available Lookup Columns'

You should be good to go. Only those rows with matching ContactIDs should be passed to the 'Match' output of the Lookup.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

wweraw25 SSC Eights! Points: 953 More actions · Answer 2

Phil Parkin - Monday, January 15, 2018 7:35 AM
wweraw25 - Monday, January 15, 2018 7:14 AM
IN OLEDB Source I've something like
Select ContactID, Name
FROM dbo.TableA
IN Lookup Transformation I need to pull only those ContactID's
I'm writing something like this
Select * FROM dbo.TableB
where ContactID = ? (How do I pass values of ContactID)
In SQL Query will look like
Select * FROM dbo.TableB where ContactID IN (Select COntactID FROM dbo.TableA)
How can I achive the same through lookup transformation??
Full cache mode
Specify how to handle rows with no matching entries: set to 'Redirect rows to nomatch output')
Connection: Use results of an SQL Query (Select ContactID FROM dbo.TableA)
Columns: Create join from dbo.TableB.ContactId to ContactId in 'Available Lookup Columns'
You should be good to go. Only those rows with matching ContactIDs should be passed to the 'Match' output of the Lookup.

Thanks for the response,
Select * FROM dbo.TableB
where ContactID = ?

If I want to write something like this, how can I capture values from OLEDB Source table
My tables are on two different servers.

Phil Parkin SSC Guru Points: 246965 More actions · Answer 3

wweraw25 - Monday, January 15, 2018 7:49 AM
Phil Parkin - Monday, January 15, 2018 7:35 AM
wweraw25 - Monday, January 15, 2018 7:14 AM
IN OLEDB Source I've something like
Select ContactID, Name
FROM dbo.TableA
IN Lookup Transformation I need to pull only those ContactID's
I'm writing something like this
Select * FROM dbo.TableB
where ContactID = ? (How do I pass values of ContactID)
In SQL Query will look like
Select * FROM dbo.TableB where ContactID IN (Select COntactID FROM dbo.TableA)
How can I achive the same through lookup transformation??
Full cache mode
Specify how to handle rows with no matching entries: set to 'Redirect rows to nomatch output')
Connection: Use results of an SQL Query (Select ContactID FROM dbo.TableA)
Columns: Create join from dbo.TableB.ContactId to ContactId in 'Available Lookup Columns'
You should be good to go. Only those rows with matching ContactIDs should be passed to the 'Match' output of the Lookup.
Thanks for the response,
Select * FROM dbo.TableB
where ContactID = ?
If I want to write something like this, how can I capture values from OLEDB Source table
My tables are on two different servers.

You cannot easily do that. Instead you have to select them all and then filter using the lookup, as described. You could also do it via a MERGE JOIN, but this will 99.9% certain be slower than a cached lookup.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

subramaniam.chandrasekar SSCarpal Tunnel Points: 4223 More actions · Answer 4

wweraw25 - Monday, January 15, 2018 7:14 AM
IN OLEDB Source I've something like
Select ContactID, Name
FROM dbo.TableA
IN Lookup Transformation I need to pull only those ContactID's
I'm writing something like this
Select * FROM dbo.TableB
where ContactID = ? (How do I pass values of ContactID)
In SQL Query will look like
Select * FROM dbo.TableB where ContactID IN (Select COntactID FROM dbo.TableA)
How can I achive the same through lookup transformation??

Please drag an OLEDB source for Table A alone.

Use lookup to compare both Table A Contact ID & B Contact ID.

Redirect unmatched rows to some other temp table OR the way you want.

****Please note that Lookup is quite slow when you work with large inputs to compare....