I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that sql server agent is not able to include jobs as job steps to be executed. I did try

  • 1
    +2 I was thinking about this last night :)
    – jcolebrand
    Jul 1, 2011 at 16:35
  • 1
    does each of your items need to be a separate job? Could they be setup into a master job with separate job steps and for each job step add t-sql or other logic which determines whether the step should run and how (e.g. if (condition = true) do work else do nothing. Jul 3, 2011 at 23:01
  • Thanks everybody for giving me some help, it seems that Aarons suggested way is getting the closest to what I need. I will see if I get permission for a tool to do it, otherwise within SQL Agent I will have to make a monster job :(.
    – nojetlag
    Jul 4, 2011 at 16:36
  • You say "packages", do you mean SSIS packages? Jul 4, 2011 at 22:19
  • agreed with John DaCosta. Use steps with conditions.
    – Alex_L
    Aug 17, 2011 at 1:02

3 Answers 3


There are 3rd party tools out there that will allow you to do this, if you don't have the learning curve time or the existing expertise to use SSIS. Disclaimer: I work for one of those companies. Check out SQL Sentry - it handles SQL Server jobs (including SSIS packages), Windows Scheduled Tasks, and even Oracle jobs. Here are the SQL Server features (including chaining and queuing):

Unfortunately I don't think you'll be able to use SQL Agent on its own to chain multiple jobs. The sp_start_job method that Marian suggested kicks off jobs asynchronously; you can't wait for them to complete before moving on to the next command or the next step.

  • Umm, I think he can query the msdb system tables though, and have a status of the other jobs (and their steps) status. If I'm wrong, I'll apologize and give you a 6 pack for revealing this :-).
    – Marian
    Jul 2, 2011 at 17:57
  • Don't understand your point. How is querying the msdb tables going to help him chain jobs together? WHILE 1=1 until the status changes? Query notifications? Sure there are many ways you can do this manually, but it's going to be a heck of a lot more complex than just calling sp_start_job. Jul 4, 2011 at 3:28

SQL Agent is not considered to be "enterprise class" as a job scheduler. It lacks a lot of functionality that you end up having to build yourself. One such example would be dependencies. As a result you are forced to put most of this logic into SSIS packages. Not necessarily a bad thing...just a pain to build and manage yourself.

So, that's my answer, build out an SSIS package and use some t-sql commands to call SQL Agent jobs as desired. Make sure you have your requirements and scenarios clearly defined. You may not want to have multiple jobs hitting the same tables at the same time, for example.


  • Silly me expecting a "enterprise" scheduler in such a mature "Enterprise Edition" product :). I had hope since i can include plenty of things as a step, funny that the most obvious (another job) is not available as executable object. I use the jobs to run SSIS packages which have plenty of complexity, having to build another SSIS package to flexibly run the jobs seems not the right approach to me. Especially since I will end up again with the asynch sp_start_job.
    – nojetlag
    Jul 4, 2011 at 16:13

You should try by using a T-SQL script in your jobs' steps, using system stored procedure sp_start_job. It will allow you to chain how many jobs you want.

  • As mentionend by Aaron, it won't allow me to keep a given sequence, since they will all fire nearly at the same time (due to the fact that they are asynch). So this is not really a chaining approach, rather a collection approach :)
    – nojetlag
    Jul 4, 2011 at 12:51
  • Ok, maybe it's not very straightforward, but you can still query the msdb system tables, you can wait for a specific time.. then query again. Or you can make a tracking table where you put the jobs steps status and based on a trigger on it you could automatically fire the next steps. Or you can buy a tool that does that and adds some more value and also shows fancy UI :-). There are options for every one..
    – Marian
    Jul 4, 2011 at 17:23

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.