Problem with Lookup Task and Full Cache

• Hawkeye112

  SSC Veteran

  Points: 240

  More actions

  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

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  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.

  ---

• Hawkeye112

  SSC Veteran

  Points: 240

  More actions

  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?

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  October 23, 2015 at 3:25 am

  #1835089

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

  Jayanth Kurup[/url]

• Hawkeye112

  SSC Veteran

  Points: 240

  More actions

  October 23, 2015 at 3:26 am

  #1835091

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

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  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]

• Hawkeye112

  SSC Veteran

  Points: 240

  More actions

  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.

• Phil Parkin

  SSC Guru

  Points: 247155

  More actions

  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.

  ---

• Hawkeye112

  SSC Veteran

  Points: 240

  More actions

  October 28, 2015 at 1:45 am

  #1835940

  Is there any other idea to my problem?

  thx

  Andreas