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
Sql-server – Chaining SQL Server Agent Jobs
sql-server-2008
Related Question
- Sql-server – Run SQL Server Snapshot Agent automatically
- Sql-server – Could not find the Server Agent in SSMS
- Sql-server – Error trying to run SSIS Packages in SQL Server 2008 SQL Server Agent
- Sql-server – How to succesfully run a batch file in an SQL Agent job
- Sql-server – Stopping SQL Server Agent: Effect on runnings jobs
- Sql-server – Cannot run Sql Server Agent jobs with PowerShell script
- Sql-server – Output File Default Location using MultiServer Agent Jobs
Best Answer
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.