September 21, 2010 at 12:12 am
Hi,
I am doing a lookup on a reference table and it returns 2 matches on the key and I want to use both the matches. Is there any way to get both the matches using lookUp or any other transformation in SSIS?
Thanks
Sharath
September 21, 2010 at 1:02 am
How about a left join? It will give you all matching values.
_____________________________________________________________
[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]
September 21, 2010 at 4:41 am
ns.sharath (9/21/2010)
Hi,I am doing a lookup on a reference table and it returns 2 matches on the key and I want to use both the matches. Is there any way to get both the matches using lookUp or any other transformation in SSIS?
Thanks
Sharath
Re-route the matched records to a destination
Raunak J
September 21, 2010 at 5:51 am
If the Lookup components has multiple matches for one row, it will only return the first one!!!
So you cannot achieve what you want with a standard lookup component.
You can implement it by using a merge join (simulating a left outer join and then throwing away all non-matched rows), but this hurts performance as the input needs to be sorted.
The easiest way is to use a left outer join in T-SQL in your source, as stated already in this thread.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 21, 2010 at 6:31 am
da-zero (9/21/2010)
.........as stated already in this thread.
Why so formal??? :-P:-D
@ns.sharath
See I told you so 😉 , do a left join anywhere (T-SQL, SSIS). That is the bset way to achieve what you want.
_____________________________________________________________
[font="Tahoma"]'Some people still are alive simply because it is illegal to shoot them... o_O 😎 '[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply