Return all matches using LookUp

  • 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

  • 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]

  • 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

  • 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

  • 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