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
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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 Event Manager from 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.
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.
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.