x

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?
more ▼

asked Feb 14, 2011 at 05:35 AM in Default

Raj More gravatar image

Raj More
1.7k 80 82 84

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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

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.

[2]: http://blogs.msdn.com/b/sqlperf/archive/2008/02/27/etl-world-record.aspx
more ▼

answered Feb 14, 2011 at 06:06 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Storage bottleneck? Talk to FusionIO at SQLBits! ahem
Feb 14, 2011 at 06:52 AM ThomasRushton ♦

@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.....
Feb 15, 2011 at 11:03 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x937
x586
x89
x86

asked: Feb 14, 2011 at 05:35 AM

Seen: 2870 times

Last Updated: Feb 14, 2011 at 05:35 AM