We have multi GB files coming every day to be loaded to SQL Server 2005 instance via SSIS packages. To load those large data files to SQL Server asynchronously, we built multi threaded .NET application. This application uses DTSExec command line executable to invoke SSIS packages. It also instantiate 5 queues and 5 streams for each queue. So, total of 25 files can be loaded at the same time.
This application, say "AsyncLoader", lives in the same machine as SQL Server 2005 instance and SSIS Instance. The server machine is huge. 32 CPU, 128 GB memory, SAN etc. We allocated 5% of the memory to the OS and the rest of the memory is reserved for SQL Server 2005 instance. Here's my options;
1) Move SSIS instance and AsyncLoader to its own machine so that DTSExec has its own large memory space to work in. Eliminate SSIS CPU cycles from SQL Server machine.
2) Keep all the players (SQL Server, SSIS, AsyncLoader) in the same machine and reduce total allocated memory on SQL Server to 75% so that DTSExec has more memory to operate.
At this time, we don't have too much time to do proof of concept work or benchmarking for each solution. I just wanted to get you folks' idea to see if someone else had that setup and what would be the best practices. Also, moving packages from one machine to another is a huge burden, so even though option 1 seems so obvious, we need hard facts to see if it brings considerable performance gains.