0

I've got an SSIS project with three packages in it. I'd like to have each package run on a different schedule from our SQL Server Agent. When I try to set up three different jobs, the schedule of each job in the project changes to the last one I set. In other words, the Agent won't let me use different schedules for the packages in the same project. Do I have to change the project to Package Deployment Mode in order for the packages to use their own schedule?

AHiggins
  • 7,029
  • 6
  • 36
  • 54
user2148983
  • 138
  • 2
  • 13
  • The short answer is, no: you can set up multiple jobs to packages (or even a single package) in the same project. Are you by chance re-using the same schedule in the 'Schedules' tab on the Job Properties window? – AHiggins May 23 '16 at 19:28
  • I tried several ways. First I created a job that had 3 steps -- one for each package execution. But if I put multiple schedules in that job, then all 3 packages will run multiple times. I don't want that. Then I tried 3 separate jobs, but as I said in my original post, SQL Server Agent won't let the 3 jobs run on different schedules. It just defaults to the last one I set among the 3 jobs. – user2148983 May 23 '16 at 19:38
  • You're right, a single job with multiple steps would not work as you were hoping. But there's no reason that three separate jobs wouldn't work. Can you verify that you are also creating unique schedules on the 'Schedules' tab? SQL Server allows multiple Jobs to use the same Schedule, so you might be mistakenly re-using and modifying the same schedule. – AHiggins May 23 '16 at 19:42
  • [This page](https://devjef.wordpress.com/2013/07/02/sql-server-agent-jobs-schedules-a-hidden-evil/) describes the situation I am talking about – AHiggins May 23 '16 at 19:44
  • So if two of the three packages need to run at the same time, say 7am every day, I'll need two separate schedules for running at 7am every day? – user2148983 May 23 '16 at 19:47
  • You could point those two at Schedule A, and the third one at Schedule B, and it would work just fine. – AHiggins May 23 '16 at 19:49
  • Got it! Thanks for your help. – user2148983 May 23 '16 at 19:54
  • Happy to help! I've converted this to an answer: if you feel it resolves your problem, please consider [accepting it](http://meta.stackexchange.com/q/5234/179419) by clicking the check-mark. This indicates to the wider community that you've found a solution and gives some reputation to both the answerer and yourself. – AHiggins May 24 '16 at 11:56

1 Answers1

0

The SQL Server Job Agent has the ability to tie multiple jobs to a single shared schedule, which can be modified from any of the jobs. That seems to be what you ran into in this case.

When setting up a unique schedule, be sure to click on the 'New...' button in the Job Properties window, rather than use the 'Pick...' button to find a pre-existing schedule.

If you would like to have two or more jobs share the same schedule (and recognize that later modifications to the schedule will affect both jobs), Pick a schedule; if you want a job to be independently executed, the create a New schedule.

AHiggins
  • 7,029
  • 6
  • 36
  • 54