none
How to clear Lookup Transformation Buffer Cache in SSIS2005

    Question


  • Hi all,
    i wonder is there anyway to clean the buffer cache of Lookup Transformation editor after running DataFlow task with lookups.
    i am using SSIS 2005
    Thanks in advance.

    Cem Uney

    cemuney
    Tuesday, September 08, 2009 10:52 AM

Answers

  • Since Lookup Cache Mode is Full, as ETL/ELTL has said the Lookup cache will be cleared and repopulated at each iteration fo the loop.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Cem Uney Tuesday, September 08, 2009 12:23 PM
    Tuesday, September 08, 2009 11:40 AM

All replies

  • Dosent SSIS do it on completion of the SSIS DFT?

    Hope this helps !!
    Please close the threads once answered

    Sudeep
    Tuesday, September 08, 2009 10:53 AM
  • i am not sure.

    i couldnt find any options to set like (clear buffer after lookup) or something else.

    Lookup Tranformation CacheType = FULL

    i have a diifferent approach in my package.
    i have a DataFlow task in For each loop container. And i want to be sure that Lookup buffer caches are cleared in every loop.


    i have a large Fact Table with 14 Lookups so i decided to split the DFT Year by Year.
    i am just testing it for performance issues.
    When i execute the full Fact table in one DFT tempdb of the SqlServer becomes very huge. So i am trying to split the DFT of Fact table by years or months.

    it is maybe stupid approach but i am just testing the performance to gain space from tempdb.

    Thanks.
    cemuney
    Tuesday, September 08, 2009 11:07 AM
  • Hi,
    as i know, SSIS clear the lookup cache after the DFT (Not sure, MSFT or Moderators can tell us)

    anyway for increasing the lookup performance you just need to Minimize the size of the reference data in memory by

    1) Use SELECT Statement (Don't select all columns of table, fetch only required columns)
    2) If possible try to use WHERE clause.
    3) DON'T
    select an entire table or view.( which returns a large amount of unnecessary data.)


    Thanks-

    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    • Proposed as answer by Noname.dtsx Wednesday, September 09, 2009 8:41 AM
    Tuesday, September 08, 2009 11:18 AM
  • Since Lookup Cache Mode is Full, as ETL/ELTL has said the Lookup cache will be cleared and repopulated at each iteration fo the loop.
    Nitesh Rai- Please mark the post as answered if it answers your question
    • Marked as answer by Cem Uney Tuesday, September 08, 2009 12:23 PM
    Tuesday, September 08, 2009 11:40 AM

  • i checked the PerfMonitor SSIS-> Buffers in use (and) Buffer memory and see that cache is cleared in each loop.
    Thanks to all anyway.


    cemuney
    Tuesday, September 08, 2009 12:05 PM
  • Hi Cem,
    Please close the thread
    Nitesh Rai- Please mark the post as answered if it answers your question
    Tuesday, September 08, 2009 12:14 PM
  • Hi,
    as i know, SSIS clear the lookup cache after the DFT (Not sure, MSFT or Moderators can tell us)


    am i right??

    Thanks-
    Let us TRY this | Don’t forget to mark the post(s) that answered your question
    Tuesday, September 08, 2009 3:44 PM