Getting SQL Server Agent to run multiple instance of a job at a time

Question

As I understand it, SQL Server Agent runs only one instance of a job (SSIS Package in this case) at a time.

I need it to Start a job 15 times, and then let them all run at the same time.

Up to now, I have accomplished this quite successfully, manually, by right clicking on the job and selecting "Execute". But i need to automate this now.

• I have tried to schedule the job to start 15 times, but it only works sequentially. (1 job after the other)
• I created 15 separate jobs and started them, but the first one starts and the others not, stating the job is already in use.

Should I look at another scheduling tool to accomplish this?

Any pointers would be welcome.....

Answer 1

As stated in the Note of MSDN Create and Attach Schedules to Jobs

Only one instance of the job can be run at a time. If you try to run a job manually while it is running as scheduled, SQL Server Agent refuses the request.

Then I'd rather rewrite the SSIS package to do the paralelism inside the package. As I don't use SSIS - except with MAintenance Plans - I cannot tell you how to do that.

Answer 2

You could dynamically create separate, identical or similar jobs, each with their own schedule to independently start at the same time, or perhaps slightly staggered.

Refer to this page: https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job