Dynamic Multi-Threaded SSIS using C#
July 9, 2012 Leave a comment
Here is a snippet of code that will assist in the dynamic ‘spin-up’ of duplicate SSIS packages. To put it simply, I used this approach in a C# service that monitored a nightly FTP file drop folder and inserted records into a queue table (one record per file) for SSIS processing. The code below essentially looks at queue table on a timer interval, gathers the count of files ready to be processed, compares that count to the number of available dtexec threads (equals the number of SSIS packages currently running minus the configurable number of SSIS threads allowed) and fires off dtexec calls as needed. Per Microsoft’s recommendation, the maximum number of concurrently running executables is equal to the total number of processors on the computer executing the package, plus two. In my case I configured this routine to have 18 max available processing threads as the dtexec server contained 16 cores. Happy coding!