question

Raj More avatar image
Raj More asked

One job runs many jobs in parallel

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?
ssissql-server-2008-r2sql-agentjob
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
WilliamD avatar image
WilliamD answered
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: [SSIS- An Inside View Part 4][1] 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][2]) The bottleneck is mostly storage, unless you *really* push the barriers, but then SQLCAT will help you out. [1]: http://www.sql-server-performance.com/articles/biz/SSIS_An_Inside_View_Part_4_p1.aspx [2]: http://blogs.msdn.com/b/sqlperf/archive/2008/02/27/etl-world-record.aspx
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Storage bottleneck? Talk to FusionIO at SQLBits! *ahem*
0 Likes 0 ·
@Thomas - I have seen FusionIO in action, it is finger lickin' good. Storage still *is* the main bottleneck for most SQL Server instances though - spinning rust is just not up to the job.....
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.