SQL Agent Job Generator: Scheduling A Job

In this last entry in the initial series of posts on building a SQL Agent Job Generator, I am going to add the ability to schedule the jobs that we generated in the previous post. The code that is available here in github: https://github.com/drsqlgithub/SSISDeployTool/tree/e8c44505bba6b3c764531c8b489101b39b5c27a7, is likely sufficient for some smaller organization’s needs with jobs. (Future entries will handle triggering jobs, validating the Json, capturing performance history, Finding differences between Json and actual jobs. SSIS jobs and deployments. (the name of the github repository is SSISDeploy for a reason)), etc).

For schedules, I will provide 3 types of schedules. Once, Weekly, and Monthly (on a specific day of the month). Jobs that are scheduled to only run Once are probably only rarely needed in this format, but it was easy enough to add so it was included. An example single execution schedule is shown here:

The first three values are the three identifiers that have been used in all of the posts. The ScheduleType is either Once, Weekly, or Monthly. JobStartDate and JobStartTime for the point in time when the job will be executed. For weekly, there are a few more choices to make:

RecurrenceFrequency tells you how often it will execute. In this example, every 2 weeks, the default is 1 if this value is not included. DaysOfTheWeek is a comma delimited list of three letter abbreviations of days of the week. Every day of the week is the default. InDayInterval can either be OnlyOnce or Minutes. JobStartTime is the time of day that the job will be executed (technically the first time, as there is an in-day recurrence parameter that will be mentioned later).

Monthly schedules are very similar to weekly, except instead of choosing the days of the week, you set the day of the month. In this example is also included a JobStartDate, and JobEndDate, which are pretty obvious (and need to be in this date format). The default JobStartDate is the current date, and the default JobStartDate is NULL, which means no end date.

Finally, the Weekly and Monthly schedules have one additional parameter set, seen in this schedule:

The InDayIntervalType of Minutes means that the job will repeat every InDayInterval minutes. So this job will repeat every 10 minutes from 12-2PM.

Note: Documentation of the process is something forthcoming as well. However, I wanted to write the code, post it, in case anyone else was struggling through the process like I was. I tried to cover as many of the bases as I could. Most of the example code I found only covered a little bit of the problem, and I did a lot of poking around and trying to get things to work (sometimes for a lot of hours!)

The rest of this blog is mostly a lot of code, which is decently commented, where needed, and should be fairly self explanatory. This first section is from the main driver function agent_CreateJobsFromJson, in the 3rd loop which does the adding of schedules:

Note: some formatting has been altered for the blog that is different from the github files.

The onetime schedules were pretty simple, and had enough different from the recurring schedules that I mad them their own function

A function that was needed for the weekly recurring schedules is to take a list of days to execute the job and change the days to binary numbers. Powershell is such a different programming language than anything I have used in the past! I probably am going to need to rename all of my functions to a different naming standard to fit in, but I don’t have time for that right now.

Finally, we add the recurring schedule to the job. There are a lot of parameters, but it seemed like no matter what that was going to be the case, so it just ad to be that way. Some things I did, like working with time taking “12:00” and using split and pushing that to New-TimeSpan seemed cheesy, but it worked and worked pretty well with the Json file so I kept it.

If you take my code and use it as a basis to build your own, one word of warning. The hardest part of this process for me was figuring out what parameters were and were not required. I struggled many times with errors that were just “failed at Create() with 0 parameters” and it was because I didn’t have a recurrenceFrequency or something when I needed one. So a lot of trial and error was required.

Ok, if I am honest, the actual hardest part of the process was that Powershell lets you do stuff like this:

And it always evaluates to $true, while setting $P_Enabled to the string value of “False”.

Now we have arrived. Going back to the TOOL_JobDeploy_BASE.ps1 file, and executing it, we see a lot of output, assuming you leave $G_VerboseDetail left to true, which I do. Then you can go to your SQL Server, and you should find the 6 jobs from the Json, five of them with schedules, including this schedule from the Postprocessing job:

Just like it was defined. The project is far from over, and I am far from done, but I will be slowing down somewhat as I was reminded by a certain editor today that I am supposed to be working on a different project already.

The framework of jobs, schedules, and environments is there for a basic job definition system, with more to follow.