I have a set of XML file downloads that will definitely be faster to run in parallel. This download and upload to database span several SSIS packages because of the complexity of the downloads and to to keep it maintainable.
If I run them in parallel, it could reduce total execution time by a significant amount.Is it possible to have one SQL Server Agent job run multiple jobs / job steps in paralleland wait for all of them to finish before going to the next step?
asked Feb 14, 2011 at 05:35 AM in Default
I think you would be better off doing the parallelism inside SSIS.
Take a look at a quick description of how it is implemented in SSIS:
As I understand it, you throw all the tasks that should run in parallel inside a data flow task container, then make sure the package is setup to allow parallel processing. SSIS then takes care to split the tasks over the number of threads you allowed and the rest is "automagic".
It can perform well too, SQLCAT (SQL Server Customer Advisory Team) demonstrated importing 1TB of Data inside 30 Minutes doing something similar to what you described ([Blog Post])
The bottleneck is mostly storage, unless you really push the barriers, but then SQLCAT will help you out.: http://blogs.msdn.com/b/sqlperf/archive/2008/02/27/etl-world-record.aspx