Post reply

Problem with Lookup Task and Full Cache

  • October 23, 2015 at 2:47 am

    #305798

    Hi,

    I have a really simple Data Flow Task with a Lookup Task in it. If I run the Task with Full Cache (inmemmory) all my records will be directed to Lookup No Match Output. If I run the package with Partial Cache or No Cache everything works fine.

    As this package ist not time-critical this will work fine for me, but I am wondering.....As I understood with Cache it should be faster but erverything else should be the same.

    So as I am really new to SSIS can anybody explain this behaviour to me.

    Thanks a lot

    Andreas

  • October 23, 2015 at 2:57 am

    #1835082

    Hawkeye112 (10/23/2015)

    Hi,

    I have a really simple Data Flow Task with a Lookup Task in it. If I run the Task with Full Cache (inmemmory) all my records will be directed to Lookup No Match Output. If I run the package with Partial Cache or No Cache everything works fine.

    As this package ist not time-critical this will work fine for me, but I am wondering.....As I understood with Cache it should be faster but erverything else should be the same.

    So as I am really new to SSIS can anybody explain this behaviour to me.

    Thanks a lot

    Andreas

    Full cache is case-sensitive. Other cache modes are not. Pretty annoying behaviour, to be honest.

    I suggest that you change everything to UPPER() before doing your comparisons & it should work.

    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

  • October 23, 2015 at 3:08 am

    #1835086

    Ok, but my lookup ist for address IDs so this is for numbers and therefore it should work, I think....

    I rechecked the data types. The data type is DT_WSTR, but there are only numbers in it. Does this matter?

  • October 23, 2015 at 3:25 am

    #1835089

    have you used a cache file , is it up to date ?

    Jayanth Kurup[/url]

  • October 23, 2015 at 3:26 am

    #1835091

    No I didn't use a Cache file, it is inmemmory.

  • October 23, 2015 at 3:28 am

    #1835092

    No; its not in memory its create a local cache file on the same server as SSIS so it improves the speed with which the lookup happens.

    Jayanth Kurup[/url]

  • October 23, 2015 at 3:38 am

    #1835097

    OK so I missunderstood this. But there comes my next question 😉

    In the Lookup Transformation Editor I didn't select Cache Connection Manager but OLE DB Connection Manager (to my lookup table), so I think it will not write a persistent caw file, but a temporary Cache file. So everytime I start the package in SSDT a new Cache file will be built.

  • October 23, 2015 at 4:37 am

    #1835113

    Jayanth_Kurup (10/23/2015)

    No; its not in memory its create a local cache file on the same server as SSIS so it improves the speed with which the lookup happens.

    You are wrong about this. In full-cache mode with OLEDB connection, the cache is in-memory.

    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

  • October 28, 2015 at 1:45 am

    #1835940

    Is there any other idea to my problem?

    thx

    Andreas

Viewing 9 posts - 1 through 8 (of 8 total)

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