locked
lookup doesn't match all rows RRS feed

  • Question

  • Hi,

    I have question on lookup, does lookup get all records from reference table (in full cache mode)? in my case look up tries to get one record from reference table (evem though 11 recrods with same ticket numer exists) and tries to compare with source records, this inserts all duplicates in my datawarehouse. i tried checksum also, no use.

    thanks in advance.
    Tuesday, September 29, 2009 5:51 AM

Answers

  • Eshwar,
    Yes- Is it our duty to make sure it gets only one record from source and reference table?

    Partial cache/ no cache is of no use becasue, once the first matching record (out of 11 matching records) is brought into memory, it will use the same record for a particular matching condition.
    Not sure- Is it default behaviour of look up to get first matching record from reference?


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Eshwar rao Tuesday, September 29, 2009 8:12 AM
    Tuesday, September 29, 2009 7:14 AM

All replies

  • Hi,

    Just do LTRIM(RTRIM(Column)) before doing the look up.
    Also check for case sensitivity.

    Redirect the error records to flat fileand try to troubleshoot.


    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Tuesday, September 29, 2009 5:58 AM
  • Hi,

    I have question on lookup, does lookup get all records from reference table (in full cache mode)? in my case look up tries to get one record from reference table (evem though 11 recrods with same ticket numer exists) and tries to compare with source records, this inserts all duplicates in my datawarehouse. i tried checksum also, no use.

    thanks in advance.

    THis behaviour is occured when you have duplicates in your reference data. As you already mentioned that there are 11 records with same ticket number. In that case is it only the ticket number you want to lookup or is there any other column along with ticket number + additional column to do the lookup?

    Regards,
    Raju
    Tuesday, September 29, 2009 6:08 AM
  • Hi,

    I have question on lookup, does lookup get all records from reference table (in full cache mode)? in my case look up tries to get one record from reference table (evem though 11 recrods with same ticket numer exists) and tries to compare with source records, this inserts all duplicates in my datawarehouse. i tried checksum also, no use.

    thanks in advance.

    Yes, lookup will get all records from the reference table in full cache mode.
    In that case, you can use lookup in partial cache or no cache mode.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, September 29, 2009 6:12 AM
  • Hi,

    I have question on lookup, does lookup get all records from reference table (in full cache mode)? in my case look up tries to get one record from reference table (evem though 11 recrods with same ticket numer exists) and tries to compare with source records, this inserts all duplicates in my datawarehouse. i tried checksum also, no use.

    thanks in advance.

    Yes, lookup will get all records from the reference table in full cache mode.
    In that case, you can use lookup in partial cache or no cache mode.


    Nitesh Rai- Please mark the post as answered if it answers your question
    Hi,
     we can use cache mode in SSIS 2008 but how can we use cache mode in ssis 2005

    Please mark the post as answered if it answers your question
    Tuesday, September 29, 2009 6:50 AM
  • Go to properties of Lookup and select the cache type you want in "Cache Type" property


    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, September 29, 2009 6:56 AM
  • Thanks all for responses.
    The 11 records i mentioned were historic data i.e with different status, dates and teams. In source also i have similar structure i.e 3 rows for each ticket. Problem i am facing is, lookup gets the first matching record and ignores remaining 10 records in reference table and matches with 3 rows in source then inserts 2 rows which are not matching.

    i tried partial/no cache but not use. is it default behaviour of look up to get first matching record from reference? Is it our duty to make sure it gets only one record from source and reference table?

    Hi Rabindra,
    we can get the cache details (full/partial) in advanced editor -> component properties of lookup.
    Tuesday, September 29, 2009 7:01 AM
  • Eshwar,
    Yes- Is it our duty to make sure it gets only one record from source and reference table?

    Partial cache/ no cache is of no use becasue, once the first matching record (out of 11 matching records) is brought into memory, it will use the same record for a particular matching condition.
    Not sure- Is it default behaviour of look up to get first matching record from reference?


    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Eshwar rao Tuesday, September 29, 2009 8:12 AM
    Tuesday, September 29, 2009 7:14 AM
  • Nitesh is correct.
    In case of multiple match Lookup takes the 1st matching record and sends it as an output.
    You have 2 options here:

    1> Modify ur query to return one record u want instead of 11 records for this case.
    2> Use a Merge Join. This will return 11 matches and then use conditional split to remove the 10 unwanted records.
    3> are the 2 tables on the same server? If yes you can use joins in the source query itself rather than using Lookup or Merge Join.
    Hope this helps !!
    Please close the thread once answered

    Sudeep      My Blog
    Tuesday, September 29, 2009 7:45 AM
  • Nitesh,

    i had filtered both source and reference to return one record but i still feel look up is not working as expected. Lookup should try to match all records from reference instead of getting first match, isn't?

    is there any other way to get it work?

    Sudeep,
    even merge jon has similar issue, unless i identify latest records i won't pass records correctly and two tables are on different servers.

    Tuesday, September 29, 2009 7:59 AM
  • Rao,
    Look up is made to work in that way there is no work around.
    As far as Merge Join is concerned it will give u 11 outputs for that one record it worls like the join statement.

    Now it is upto u to remove the unwanted records, how u do it u need to figure it out, may be using other columns which may help you achieve this.
    Hope this helps !!
    Please close the thread once answered

    Sudeep      My Blog
    Tuesday, September 29, 2009 8:03 AM
  • Understood friends. Thanks for the help.
    Tuesday, September 29, 2009 8:13 AM