Hi, We are migrating our production environment from 32-bit SQL Server 2005, Windows 2003 server to 64-bit SQL Server 2005, Windows 2003 server environment with 4GB of RAM. We have recompiled the SSIS packages to run in 64-bit mode and stored them in database. But one of the package fails with initial information as Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 34720 buffers were considered and 34720 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked. And the subsequent error messages received as Error Message 1 A buffer could not be locked. The system is out of memory or the buffer manager has reached its quota. Error Message 2 An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from DTS buffer timed out.". Please guide us in troubleshooting this issue. Thanks & RegardsAmit

Does this package fail as part of a group of packages, or when it is run by itself? Does it contain lookup components?

The package contains lookup component. This package if ran in isloation 90% of time runs but when ran as a group of package it fails. ~ Amit

Try enabling memory restriction on the advanced tab of the lookup. This prevents the lookup from caching all the data in memory when the data flow is first loaded. I've seen this error before when working with a very large amount of data in my lookup. If this works, it indicates that the lookup is trying to load too much into memory in conjunction with the other packages. There is a disadvantage associated with enabling memory restriction: your performance will often slow down. Workarounds for this in suggested order: Make sure the lookup is caching the bare minimum of data needed. If you have selected a table in the lookup, switch it to a SELECT with as few columns as possible. This minimizes the memory usage. Add constraints so that this packagedoes not run in parallel with other packages. This mayfree enough memory to successfully run the package. Check your other packages to make sure theyaren'tusing more memory than necessary. Hope this helps.

Hi John, I am having the same problem here. Out of interest how much data do you consider as large amounts for SSIS lookups. Iam running on SQL2008 (NovCTP) and my package is erroring with multiple "the buffer manager detected that the system was low on virtual memory" errors. I am using cached lookups on about 400,000 rows - this this excessive? I have tried reducing DefaultBufferSize & DefaultBufferMaxRows but still can't prevent the memory errors. Cheers, Steve.

Number of rows is important, but so is the width of the rows in the lookup - how many columns are you specifying? I've done millions of rows in a lookup without problems. "Large" really depends on your hardware, etc. Since this is on 2008, it might be a problem in the CTP - they have changed a few things in the lookup. Have you posted on the Katmai forum? (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=1622&SiteID=1)

Thanks for the reply jwelch. I am using the default "select * ...." in my lookups. I could restrict it on 1 column I guess- will this make much difference though?I have 5 lookups in a foreach file loop - it usually errors in the first loop then stops. My lookup dimension tables contain a few thousand rows each. I am trying to load from flat-files each containing about 12million rows. Hardware is Windows2008, 8GB RAM, 64bit. I haven't posted in the Katmai forum as I get the same problem on 2005 SSIS too. Steve.

Yes, selecting on one column can make quite a difference. If you are retrieving one integer column, that's 4 bytes of memory for each row. If you are retrieving all the columns, it might be quite a bit more data. It is a best practice in SSIS to never use SELECT *, especially for Lookups. Always specify the smallest column list possible in lookups.

Steve Rodgers wrote: Thanks for the reply jwelch. I am using the default "select * ...." in my lookups. I could restrict it on 1 column I guess- will this make much difference though?I have 5 lookups in a foreach file loop - it usually errors in the first loop then stops. My lookup dimension tables contain a few thousand rows each. Yes! This can make a huge difference. The details depend on the lookup table (how many columns, and their data types) but this is probably the single most important thing you can do with non-cached lookups to improve performance.

MatthewRoche wrote: Yes! This can make a huge difference. The details depend on the lookup table (how many columns, and their data types) but this is probably the single most important thing you can do with non-cached lookups to improve performance. I'd say it's as important with cachedlookups, as it minimizes the memory footprint of the cache. More available memory usually translates to faster processing.

jwelch wrote: MatthewRoche wrote: Yes! This can make a huge difference. The details depend on the lookup table (how many columns, and their data types) but this is probably the single most important thing you can do with non-cached lookups to improve performance. I'd say it's as important with cachedlookups, as it minimizes the memory footprint of the cache. More available memory usually translates to faster processing. Ugh... I don't even know why I put "non-cached" in there at all. Too much turkey this long holiday weekend...

Okay, I am trying to reduce my lookup size as suggested. However, editing the generated "select * from (select * from ....)..." appears to give errors. Why does SSIS always generate this as a sub-select? Can I scrap that any just use a simple select with a ? parameter. eg. I am trying to achieve this.... select AreaCodeKey from [Cube].[DimAreaCode] where [Cube].[DimAreaCode].[AreaCodeNativeKey] = ? But now SSIS complains with an OLE-DB error code!

Can you please post that complete OLE DB error here?

Matthew, I've managed to sort the OLE-DB error now. I am just re-writing all my lookups with specific columns. Steve.

Rats! I thought I had it sorted then but it's just blown again!! I amended all5 lookups to only retreive the columns needed, made them all full cache and removed any duplicates. It's good for about 10million rows then it starts reporting memory errors (see below). Information: 0x400490F4 at Load current file in DB, Lookup Call Class [3189]: component "Lookup Call Class" (3189) has cached 421 rows.Information: 0x400490F5 at Load current file in DB, Lookup Call Class [3189]: component "Lookup Call Class" (3189) has cached a total of 421 rows.Information: 0x400490F4 at Load current file in DB, Lookup Area Code [3132]: component "Lookup Area Code" (3132) has cached 2730 rows.Information: 0x400490F5 at Load current file in DB, Lookup Area Code [3132]: component "Lookup Area Code" (3132) has cached a total of 33992 rows.Information: 0x400490F4 at Load current file in DB, Lookup Fixed Call Type 'F' [3261]: component "Lookup Fixed Call Type 'F'" (3261) has cached 13 rows.Information: 0x400490F5 at Load current file in DB, Lookup Fixed Call Type 'F' [3261]: component "Lookup Fixed Call Type 'F'" (3261) has cached a total of 13 rows.Information: 0x400490F4 at Load current file in DB, Lookup Customer (max) [3334]: component "Lookup Customer (max)" (3334) has cached 1365 rows.Information: 0x400490F5 at Load current file in DB, Lookup Customer (max) [3334]: component "Lookup Customer (max)" (3334) has cached a total of 341998 rows.Information: 0x4004300C at Load current file in DB, SSIS.Pipeline: Execute phase is beginning.Information: 0x4004800C at Load current file in DB, SSIS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 474 buffers were considered and 464 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.

Steve, on the data flow, have you specified a directory for the BufferTempStoragePath parameter? Try doing that.Also, I just want to be sure, you're issuing a "select column from table" in the "reference table" tab, not in the "advanced tab" right?

Moderator, can you please merge this back into the original thread? Steve, how wide (what columns of what data types) is the lookup query for the "Lookup Customer (max)" transform?

Phil. Yes, I have specified a directory for BufferTempStoragePath and during execution I can see files being generated in there. Yes, I am using the "reference table" tab that's right. I did previosuly try changing the advance tab also, but now i've left that alone.

MatthewRoche wrote: Steve, how wide (what columns of what data types) is the lookup query for the "Lookup Customer (max)" transform? My "Lookup Customer (max)" selects 3 columns using "select CustKey, CustNativeKey, CLINum from Cube.CustLookup" CustKey integer CustNativeKey varchar(14) CLINum varchar(13) There are 341,998 rows in the lookup table.

I get this error in my 64-bit Development Environment (Win 7 x64, Office 2010 x64, VS 2008/BIDS) using a very simple Excel document. I have set the "Run64Runtime" project setting to False to get this far. I'm using a Fuzzy Lookup shape as I can't get the standard lookup shape to work. Any help would be appreciated.