Call Multiple SQL Jobs In Sequence Via PowerShell

Recently I had an issue come up where I needed to run about 15 SQL Agent Jobs in a specific order, bouncing back and forth across multiple SQL Servers, ensuring that each job completed successfully before starting the next job in the series.  On top of that, I was going to have to perform this action multiple times a day, randomly, for several days.  After doing this manually a couple of times, I realized that I would be much better off creating a PowerShell script to do this for me.

To lay out the scenario of what I was trying to accomplish, I had 15 jobs spread across 3 SQL Servers that needed to be called as follows:

  • Server 1 – Job 1, Job 2, Job 3, Job 4, Job 5
  • Server 2 – Job 6
  • Server 1 – Job 7, Job 8
  • Server 3 – Job 9, Job 10
  • Server 1 – Job 11, Job 12, Job 13, Job 14, Job 15

 

To accomplish this, I wrote the following quick and dirty PowerShell script.  The script allows you to pass in a server name and an array of job names, and it will connect to the specified SQL server and run the jobs in the order they are listed, waiting for each job to complete successfully before starting the next job.  In the event a job fails (or completes with any status other than “succeeded”), the script will stop processing and not run any further jobs.

This script can probably be refined a little bit, with some error handling thrown in, but it is only being used temporarily and does what I need so I’m happy with it.  I’m just running it locally from within my IDE so I just pull it up, click “go”, and come back in a half hour to check on it.  Now there’s no need to have to call each job manually and wait for it to complete!

 

(Visited 2,229 times, 1 visits today)

1 thought on “Call Multiple SQL Jobs In Sequence Via PowerShell

Comments are closed.