I have a simple data flow task that moves logging events from one database to another. It pulls log events using an OleDb datasource (tried using data access mode of "Table or View" as well as "SQL Command"). Then it does multicast, and an OleDb lookup of data in 5 related tables to denormalize. I've tried using full cache, partial cache, and no cache for each of the lookup tasks. Then it inserts into one of the 5 corresponding logging tables, which I've tried using the OleDb destinations with and without fast load. The source and destination databases are all on another server.
It seems to work fine in small volume (<50,000 records), but crashes at random points with higher volume. The point it crashes at varies in about 10 test runs, sometimes as low as after 70,000 records pulled from the datasource, and sometimes as high as 980,000 records pulled from the datasource. I'm trying to run it through 1,200,000 records, and it has come close but crashes before it completes every time.
I've tried executing in BIDS while debugging, but it just stops and the output window shows "Finished: Canceled". I've also tried from the command line as well as SQL Agent, executing under a few different accounts from network service to domain administrator. Each crash logs a dump file to the file system, which shows the exception "The thread tried to read from or write to a virtual address for which it does not have the appropriate access".
In some readings, it seems this is resource related, virtual memory problem maybe? The system has 16GB of RAM and runs integration services almost exclusively. The process seems to take up 1.5GB of RAM at the peak, and when I monitor resources it seems to never have less than about 8GB of RAM free in the system, so RAM doesn't seem to be a choke point.
I've tried raising and lowering the "DefaultBufferMaxRows", "DefaultBufferSize" and "EngineThreads", but without any real understanding of their impact. In any event, it doesn't seem to affect stability if I modify those values. I've also tried specifying the "BufferTempStoragePath" to a fast SSD drive with plenty of space, but the data flow never seems to need the buffer temp storage - it stays empty.
By default, there's 16GB of swap space on the system drive, and I've tried tripling it, adding 16GB to two other drives each, but doesn't seem to impact it.
This system is a Windows Server 2008 R2 Virtual Machine running in a Windows Server 2012 Hyper-V Host. SQL is Standard 2012.