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!
Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence
I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.