My issue is loading 140 million records into a Cache Transformation fails constantly since upgrading to SSDT 2013 from 2010.
The error is reproducible 100% of the time while running the package in debug mode (dtsDebugHost.exe).
The server is rather large having the following configuration.
Windows Server 2012 Datacenter
Processor Xenon E5-4650 @ 2.70 GHz (4 proc)
768 GB RAM
SQL Server Max Memory 600 / Min 520GB
This issue has also been tested on a different server with similar results.
The source of the Cache is an OLE DB Connection to the local SQL server pulling 13 columns all having ID values.
The destination is a standard Cache Destination.
The errors are as follows and occur while both writing to file and full memory cache near 20.5 million records for both Cache types:
[SSIS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 14 buffers were considered and 14 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.
Information: Buffer manager allocated 4 megabyte(s) in 2 physical buffer(s).
Error: A buffer failed while allocating 1703856 bytes.
Error: The system reports 92 percent memory load. There are 412146331648 bytes of physical memory with 29133062144 bytes free. There are 4294836224 bytes of virtual memory with 8069120 bytes free. The paging file has 429326200832 bytes with 43001991168 bytes free.
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Cache LicenseMaster" (2) failed with error code 0xC0208252 while processing input "Cache Input" (5). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Our initial thoughts are that SQL server is not configured correctly to handle the usage of so many buffers.
I have tried both extremes of Cache property settings for MAXROWS and MAXBUFFERSIZE.
I have read many articles, but none seems to discuss buffer management in detail.
We appreciate your insights and recommendations.
Scott M