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:
|
1 2 3 4 5 6 7 8 9 |
{ "SystemName": "SalesProcessing", "SubsystemName": "_MasterSalesProcessing", "EnvironmentName": "GeneralOperation", "ScheduleType" : "Once", "JobStartDate" : "2020-04-15", "JobStartTime" : "12:00", "Notes" : "Every 3 months on the 15th at Noon" }, |
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:
|
1 2 3 4 5 6 7 8 9 10 11 |
{ "SystemName": "OrderProcessing", "SubsystemName": "_MasterOrderProcessing", "EnvironmentName": "GeneralOperation", "Enabled":"False", "ScheduleType" : "Weekly", "RecurrenceFrequency" : "2", "DaysOfTheWeek" : "MON,TUE,WED,THU,FRI,SAT,SUN", "JobStartTime" : "12:00", "Notes" : "Once every other week, every day" }, |
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).
|
1 2 3 4 5 6 7 8 9 10 11 12 |
{ "SystemName": "OrderDataWarehouse", "SubsystemName": "_MasterOrderDataWarehouse", "EnvironmentName": "GeneralOperation", "ScheduleType" : "Monthly", "RecurrenceFrequency" : 3, "MonthlyDayOfTheMonth": 6, "JobStartDate" : "2020-04-15", "JobStartTime" : "12:00", "JobEndDate" : "2021-04-15", "Notes" : "Every 3 months on the 15th at Noon" }, |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
{ "SystemName": "PostProcessing", "SubsystemName": "_PostProcessing", "EnvironmentName": "GeneralOperation", "ScheduleType" : "Weekly", "DaysOfTheWeek" : "MON,WED,FRI", "JobStartTime" : "12:00", "JobEndTime" : "14:00", "JobEndDate" : "2020-05-10", "InDayIntervalType": "Minutes", "InDayInterval" : 10, "Notes" : "Mon, Wed, Fri from 12-2PM, every 10 minutes" } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
for ($i = 0; $i -lt $itemsI ; $i++) { $L3_SystemName = $ScheduleItems.JobSchedule[$i].SystemName $L3_SubsystemName = $ScheduleItems.JobSchedule[$i].SubsystemName $L3_EnvironmentName = $ScheduleItems.JobSchedule[$i].EnvironmentName $L3_ScheduleType = $ScheduleItems.JobSchedule[$i].ScheduleType $L3_RecurrenceFrequency = $ScheduleItems.JobSchedule[$i].RecurrenceFrequency $L3_DaysofTheWeek = $ScheduleItems.JobSchedule[$i].DaysOfTheWeek $L3_InDayInterval = $ScheduleItems.JobSchedule[$i].InDayInterval $L3_InDayIntervalType = $ScheduleItems.JobSchedule[$i].InDayIntervalType $L3_MonthlyDayOfTheMonth = $ScheduleItems.JobSchedule[$i].MonthlyDayOfTheMonth $L3_JobStartDate = $ScheduleItems.JobSchedule[$i].JobStartDate $L3_JobStartTime = $ScheduleItems.JobSchedule[$i].JobStartTime $L3_JobEndDate = $ScheduleItems.JobSchedule[$i].JobEndDate $L3_JobEndTime = $ScheduleItems.JobSchedule[$i].JobEndTime $L3_Enabled = $ScheduleItems.JobSchedule[$i].Enabled #default the start date if (!$L3_JobStartDate) {$L3_JobStartDate = Get-Date} #default the days of the week if (!$L3_DaysOfTheWeek) {$L3_DaysOfTheWeek = "SUN,MON,TUE,WED,THU,FRI,SAT"} #default recurrency frequency to be every 1 time period (week or month) if (!$L3_RecurrenceFrequency) {$L3_RecurrenceFrequency = 1} #default enabled to enabled if (!$L3_Enabled) {$L3_Enabled = "True"} #etch the name of the agent job you are adding the schedule to $L3_AgentJobName = SQLAgent_FormName -P_SystemName $L3_SystemName -P_SubSystemName $L3_SubSystemName ` -P_EnvironmentName $L3_EnvironmentName -P_JobStartType "Scheduled" #Part of the definition of having a schedule #onetime schedules are a lot easier, so I made it it's own function if ($L3_ScheduleType -eq "Once") { agent_AddOneTimeScheduleToJob -P_ServerName $P_ServerName -P_JobName $L3_AgentJobName ` -P_jobStartDate $L3_jobStartDate -P_JobStartTime $L3_JobStartTime ` -P_Enabled $L3_Enabled } else { #monthly and weekly schedules varied only with the day/days it ran, so it became one call agent_AddRecurringScheduleToJob -P_ServerName $P_ServerName -P_JobName $L3_AgentJobName ` -P_ScheduleType $L3_ScheduleType -P_RecurrenceFrequency $L3_RecurrenceFrequency ` -P_DaysOfTheWeek $L3_DaysOfTheWeek -P_InDayIntervalType $L3_InDayIntervalType ` -P_InDayInterval $L3_InDayInterval -P_JobStartDate $L3_JobStartDate ` -P_JobStartTime $L3_JobStartTime -P_JobEndDate $L3_JobEndDate ` -P_JobEndTime $L3_JobEndTime -P_MonthlyDayOfTheMonth $L3_MonthlyDayOfTheMonth ` -P_Enabled $L3_Enabled } } } |
The onetime schedules were pretty simple, and had enough different from the recurring schedules that I mad them their own function
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
function agent_AddOneTimeScheduleToJob($P_ServerName, $P_JobName, $P_jobStartDate, $P_JobStartTime, $P_Enabled) { #use to add a onetime schedule to a job Try { if ($G_VerboseDetail) { write-host "Adding onetime schedule to $P_JobName" } #connect to the sql server, and then connect to the job that was passed in $SQLServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($P_ServerName) $sqlJob = $SQLServer.JobServer.Jobs[$P_JobName] #create the new schedule $sqlJobSchedule = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($sqlJob, "$p_JobName") #give it an obvious name $sqlJobSchedule.Name = "ONETIME-$P_JobName-$JobStartTime" #enable the job based on the setting if ($P_Enabled -eq "False") { $sqlJobSchedule.IsEnabled = $false } Else { $sqlJobSchedule.IsEnabled = $true } $sqlJobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::OneTime #set the start time from the required prarmeter $TimeList = $P_JobStartTime -split ":" $jobStartTime = New-TimeSpan -Hour $TimeList[0] -Minute $TimeList[1] $sqlJobSchedule.ActiveStartTimeofDay = $JobStartTime #set the job start date from a standard data format $JobStartDate = [datetime]::parseexact($P_JobStartDate, 'yyyy-MM-dd', $null) $sqlJobSchedule.activeStartDate = $JobStartDate #create the schedule $sqlJobSchedule.Create() #alter the job $sqlJob.Alter() } Catch { Write-Error $_ write-host "Error adding schedule to $P_JobName"; THROW } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
function agent_SplitFrequency($P_JobFrequencyIntervalList) { #return the bitmask for the days of the week that correspond #to the list in the schedule try { #split the items in the frequency list (which we do # not check for dups or misspellings, so that is up to you) $ItemList = $P_JobFrequencyIntervalList -split "," $items = $ItemList.length $frequencyIntervalOutput = 0 for ($i = 0; $i -lt $items ; $i++) { switch ($ItemList[$i]) { "SUN" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 1 } "MON" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 2 } "TUE" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 4 } "WED" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 8 } "THU" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 16 } "FRI" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 32 } "SAT" { $frequencyIntervalOutput = ` $frequencyIntervalOutput + 64 } } } Return $FrequencyIntervalOutput } catch { Write-Error $_ write-host "Error Splitting String For Frequency" THROW } } |
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:
|
1 |
if ($P_Enabled = "False") { |
And it always evaluates to $true, while setting $P_Enabled to the string value of “False”.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 |
function agent_AddRecurringScheduleToJob($P_ServerName, $P_JobName, $P_ScheduleType, $P_RecurrenceFrequency, ` $P_DaysOfTheWeek, $P_InDayIntervalType, $P_InDayInterval, $P_jobStartDate, $P_JobStartTime, ` $P_jobEndDate, $P_JobEndTime, $P_MonthlyDayOfTheMonth, $P_Enabled){ Try { #connect to the sql server, and then connect to the job that was passed in $SQLServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server($P_ServerName) $sqlJob = $SQLServer.JobServer.Jobs[$P_JobName] #split the time into minutes and seconds, feed that to a timespan $TimeList = $P_JobStartTime -split ":" $jobStartTime = New-TimeSpan -Hour $TimeList[0] -Minutes $TimeList[1] #create a new schedule $sqlJobSchedule = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobSchedule') ($sqlJob, "$P_JobName") #provide a slightly descriptive name $sqlJobSchedule.Name = "$P_ScheduleType $P_DaysOfTheWeek $jobStartTime" #set enabled status of the job if ($P_Enabled -eq "False") { $sqlJobSchedule.IsEnabled = $false } Else { $sqlJobSchedule.IsEnabled = $true } #set the start date and time of the job $sqlJobSchedule.activeStartDate = $P_jobStartDate $sqlJobSchedule.ActiveStartTimeofDay = $JobStartTime #set how often the job recurs, based on requency type $sqlJobSchedule.FrequencyRecurrenceFactor = $P_RecurrenceFrequency if ($P_ScheduleType -eq "Weekly"){ $sqlJobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Weekly #for weekly uses the bitmask from the weekdays $JobFrequencyIntervalNumeric = agent_SplitFrequency($P_DaysOfTheWeek) $sqlJobSchedule.FrequencyInterval = $JobFrequencyIntervalNumeric } elseif ($P_ScheduleType -eq "Monthly") { $sqlJobSchedule.FrequencyTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Monthly #for montly uses the day of the month (could get kind of ugly error if you put in illogical day) if (!$P_MonthlyDayOfTheMonth) {$P_MonthlyDayOfTheMonth = 1} $sqlJobSchedule.Frequencyinterval = $P_MonthlyDayOfTheMonth } else { Throw "Unsupported Scheduletype value: [$P_ScheduleType]" } #Enddate is not required, so I did not default this If ($P_jobEndDate){ $sqlJobSchedule.ActiveEndDate = $P_JobEndDate } #The default is onlyonce, but if you choose minutes for in day interval type, you can choose to have things repeat multiple times if ($P_InDayIntervalType -eq "Minutes") { $sqlJobSchedule.frequencySubDayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Minute #this is now the number of minutes in the interval $sqlJobSchedule.frequencySubdayInterval = $P_InDayInterval #it repeats until this time, which we convert to a time span if (!$P_JobEndTime){ $P_JobEndTime = "23:59:59" } $EndTimeList = $P_JobEndTime -split ":" IF (!$EndTimeSeconds) { $EndTimeSeconds = 59} $jobEndTime = New-TimeSpan -Hour $EndTimeList[0] -Minutes $EndTimeList[1] -Seconds $EndTimeSeconds $sqlJobSchedule.ActiveEndTimeofDay = $JobEndTime } Else { #otherwise it is a onetime operation $sqlJobSchedule.frequencySubDayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Once $sqlJobSchedule.frequencySubdayInterval = 0 } #create the schedule $sqlJobSchedule.Create() #alter the job $sqlJob.Alter() if ($G_VerboseDetail){ Write-Host " Added schedule to $P_JobName" } } Catch { Write-Error $_ write-host "Error adding schedule to $P_JobName"; THROW } } |
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.
Load comments