locked
Lookup on multiple columns with multiple look tables RRS feed

  • Question

  • Hi,

           I have a oledb source which generates a dataset with columns x,y,z... I need to lookup on these columns on tables xlookup, ylookup, zlookup.

    So my resultant output dataset after 3 lookups should look like x,x1,y,y1,z,z1 where x1,y1,z1 are the corresponding lookup table output columns.

    If there is a no match, null should be populated as the lookedup value.

    I tried to use lookup transformation, but the output of one lookup has a condition (match or nomatch) to redirect the output to another lookup. The output of one column lookup is no way dependent on the other as they are independent lookups.

    Please suggest which task should I need to use for these multiple lookups.

    Wednesday, November 7, 2012 3:48 PM

Answers

  • Use three separate Lookup Transformations with the no match behavior set to "Ignore Failure." The NULLs will take care of themselves.
    • Marked as answer by nidabp Thursday, November 8, 2012 2:13 PM
    Thursday, November 8, 2012 12:20 AM

All replies

  • I think you need to dump the data into a staging table 1st. Then use the MERGE T-SQL command against each target (3) to set the source table fields (the staging one) updated as you stated

    Btw, I assumed originally you have values as

    Col1  Col2  Col3

    test   blah   wow

    Then you may match as follows

    test blah, bla wow, bingo


    Arthur My Blog

    Wednesday, November 7, 2012 3:58 PM
  • Hi Arthur,

       Sorry if I was unable to explain you properly.Assuming I have a single record in all my tables.

    If my dataset has columns x,y,z with values (1,2,3)

    and my first loolup table has columns x,x1 with values(1,a)

    and my second lookup table has columns y,y1 with values(2,b)

    and my third lookup table has columns z,z1 with values(4,c)

    then my output should have a dataset with columns(x,x1,y,y1,z,z1) with values (1,a,2,b,3,null)

    Do I need to dump the data into a  staging table? or Is it possible to do lookup on the fly using the lookup component.

    Thank you.

    Wednesday, November 7, 2012 4:49 PM
  • Use three separate Lookup Transformations with the no match behavior set to "Ignore Failure." The NULLs will take care of themselves.
    • Marked as answer by nidabp Thursday, November 8, 2012 2:13 PM
    Thursday, November 8, 2012 12:20 AM
  • Thank you D.Pendleton.

    Thursday, November 8, 2012 2:13 PM