2

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.....

1
  • Can you post the screenshot of the error when you start all the jobs simultaneously?
    – SQLPRODDBA
    May 20, 2016 at 8:51

2 Answers 2

3

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.

1
  • 1
    As a solution i created a SSIS Package with multiple "Execute Process Task" tasks. The server, then runs the amount of Tasks that is equal to its CPU count, plus 2, in parallel. Thus you have to schedule only one job with SQL Server Agent, that spawns multiple jobs (Up to # CPU +2).
    – stefanl
    Dec 14, 2016 at 8:26
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.