SSIS Buffer/Memory Failure
2,147,352,576 bytes is 2Gb which implies you have a 32-bit SSIS running on 32-bit Windows, so the 16Gb total memory of which 10.9 is free is fairly meaningless but is where the 31% comes from: (17,179,328,512 - 11,714,314,240) / 17,179,328,512 = 0.318.

A 32-bit process can only use 2GB (or under some configurations 3Gb) unless it is compiled LARGEADDRESSAWARE and running on a 64-bit edition of Windows. On 64-bit Windows a large-address aware process should be able to use 4Gb (see this which refers to here). 

In this case the process seems to have ~386Mb available in the 2GB address space and is failing to allocate 100Mb which is less than that - one educated guess is that this could be due to fragmentation: if SSIS is wanting a single coherent block and the remaining free address space is in blocks smaller than that spread around the address space.

No new SSIS packages have been added.


The change is likely due to increases in incoming data size in that case.

Possible Solutions

If you are running a 32-bit Windows edition you could try using the /3GB switch to ask Windows to use the 3+1 address space arrangement for 32 bit processes instead of 2+2 (see https://technet.microsoft.com/en-us/library/bb124810(v=exchg.65).aspx or similar reference for your edition of Windows) - if SSIS is capable of using the larger address space this will give it a bit more room.

If you are using a 64-bit Windows install and 64-bit SQL then try to make SSIS use its 64-bit run-time. There are issues with some data access drivers here though so that may not work. Also the design-time environment is always 32-bit. See https://technet.microsoft.com/en-us/library/ms141766(v=sql.105).aspx and other documentation.

In either case you may be able to break down your ETL process into smaller packages and chain them together with Execute Package tasks. With the "ExecuteOutOfProcess" option turned on each package called should be able to use its own 2Gb address space.

You might also try tweaking the way buffers are allocated in data flow tasks using the DefaultBufferMaxRows and DefaultBufferMaxSize properties; see here, here and here.