3

There is an SQL Agent Job containing a complex Integration Services Package performing some ETL Jobs. It takes between 1 and 4 hours to run, depending on our data sources. The Job currently runs daily, without problems. What I would like to do now is to let it run in an endless loop, which means: When it's done, start over again.

The scheduler doesn't seem to provide this option. I found that it would be possible to use the steps interface to go to step one after the last step is finished, but there's a problem using that method: If I need to stop the job, I would need to do that in a forceful way. However I would like to be able to let the job stop after the next iteration. How can I do that?

Thanks in advance for any help!

Thomas Tschernich
  • 1,264
  • 15
  • 29
  • Is it really difficult, if you need to stop the job, to edit the last step to *not* go back to step 1 and then wait? – Damien_The_Unbeliever Jul 08 '13 at 08:50
  • 1
    Just schedule it to run every minute. If it is already running then this won't cause a second instance of the job to be started. – Martin Smith Jul 08 '13 at 08:54
  • 2
    Just schedule the job with high frequency, eg. every 10 minutes. If the job is already running, it will not be re-launched. If is not running will start again. – Remus Rusanu Jul 08 '13 at 09:09
  • Didn't know it will work this way. That's a simple but perfect solution then. If I want to let the stop job after the next iteration I can simply disable the schedule. Thanks a lot! – Thomas Tschernich Jul 08 '13 at 10:59
  • @MartinSmith or RemusRusanu - can one of you please post an answer so I can accept it? – Thomas Tschernich Jul 09 '13 at 07:52

4 Answers4

3

Since neither Martin nor Remus created an answer, here is one so the question can be accepted. The best way is to simply set the run frequency to a very low value, like one minute. If it is already running, a second instance will not be created. If you want to stop the job after the current run, simply disable the schedule.

Thanks!

Thomas Tschernich
  • 1,264
  • 15
  • 29
1

So you want that when you want to stop the job, after the running iteration, it should stop - if I am getting you correctly.

You can do one thing here.

  1. Have one table for configuration which is having boolean value.
  2. Add one step into the job. i.e. Before iteration, check the value from table. If it's true, then only run the ETL packages.
  3. So, each time it finds its true, it'll follow endless loop.
  4. When you want to stop the job, set that value in table to false.
  5. When the current job iteration completes, it'll go to find the value from your table, will find it false, and the iteration will stop.
Paritosh
  • 11,144
  • 5
  • 56
  • 74
1

you can always set the "on success" action to go to step one, creating an endless loop, but as you said, if you want to stop the job you'll have to force it.

Other than that, an simple control table on the database with a status and a second job that queries this table and fires your main job depending on the status. Coupe of possible architectures here, just pick the one that suits you better

Diego
  • 34,802
  • 21
  • 91
  • 134
1

You could use service broker within the database. The job you need to run can be started by queuing a 'start' message and when it finishes it can send itself a message to start again.

To pause the process you can just deactivate the queue processor.

Rikalous
  • 4,514
  • 1
  • 40
  • 52