Hi, I am facing a problem with Lookup component in SSIS. I need to lookup from a transaction table for getting some info, But when im trying to implement the same, the Pre-Execute step itself got failed saying like, [DTS.Pipeline] Information: The buffer manager failed a memory allocation call for 524264 bytes, but was unable to swap out any buffers to relieve memory pressure. 9467 buffers were considered and 5956 were locked. Either not enough memory is available to the pipeline because not enough are installed, other processes were using it, or too many buffers are locked. [Tracer [19717]] Error: A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota. [DTS.Pipeline] Error: component "Tracer" (19717) failed the pre-execute phase and returned error code 0xC020204B. Component Tracer is the Look up. Tracer is having around 6.5 mil records. Is there any way to allocate more buffers thru buffer manager? Or is there any alternative to solve this problem? FYI, the hard disk free space is more than 250 GB. Thanks in advance.

Perhaps this might be of help: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=410690&SiteID=1 -Jamie

Thanks for your reply, But, the above s something related to virtual memory, This wont help us to solve the problem.. We tried to change our Virtual memory settings already.

In your Lookup Task are you looking up a specific table, or are you using a SQL command to select only the columns in the table which you need? If it's caching the entire table rather than just the 2 or 3 columns which you (probably) need it's very memory hungry. Try a SQL command.Greg.

Thanks or ur reply. Ya, i tried by selecting the columns which i need, still having the same problem.

Hi Swarna, Were you able to find a solution for this problem? I am new to SSIS and I am getting the same error. If you found the problem and sol, can you please point me in the right direction? Thanks.

Hi Sam, I can not find a soluton for the problem but i found an alternative to solve that. I am not sure this alternative will solve your case or not. In our case, we are planning to schedule the package execution as a job in daily basis. So, its sufficient for us to have a look up in the new coming data, that is for only that day data. So i have created a temporary table to have only one day data. Then look up into that temp table. Each day clear the temp table then store the new data. So the Look up wont say there is no buffer space etc... This is not a solution, a work around. Still i am searching for a better solution. Thanks. Swarna.

Yes. All cached data must fit in memory for a lookup to work. You can disable caching but that hurts performance. The usual workaround is to engineer a way to reduce the size of the cached data. Reducing the number of columns is a good start. Another common approach is to send the keys of the lookup to SQL Server in advance in a previous dataflow or package. Then have a view that joins the full data to be cached to the keys table. Once that is in place point the lookup at the view.

Have you tried to decrease DefaultBufferMaxRows to 10 lets say?

What type of caching you are using ? Full Or Partial ?

This thread is almost two years old... After a few service packs even.

Hi Look up always degrade the package performance because it cache all the records in memory . i came across the same situation where my component was taking 3 hrs to look up 40 million records . i replaced the look up with a different logic . lets take a example of Empid suppose you are fetching records from source and using look up on empid with Destination database table (reference table ) first put all the records in a temp table of destination table Now use one inner join with temp table to have matched records output and use not in query for the unmatched records put . this logic is taking few minutes and saved 2.3 hrs . let me know if you have any problem