In this entry in the SQL Agent Job Generator system series, we are going to turn the corner and actually start producing working SQL Agent jobs. We will start with simple, standalone T-SQL jobs, that you can kick off or schedule (and that is how I have labeled them, as scheduled since that is how they will be implemented in the next entry.) Triggering one job from another, and on-demand jobs will also follow on later.
The entire project’s code is available on github here: https://github.com/drsqlgithub/SSISDeployTool/tree/2e4a26111e4eb79a21fbfc7a1e0d82fff6c76774
For this iteration, I am going to add a set of columns to the JOBS_Definition.json file to define the tasks of the job. We will describe the job, define how it is started, what type of code it will execute, the text and database where that code will execute of the job (if applicable.)
|
1 2 3 4 5 6 7 8 9 10 11 12 |
"Jobs": [ { "SystemName": "SalesProcessing", "SubsystemName": "_MasterSalesProcessing", "EnvironmentName": "GeneralOperation" "EnvironmentName": "GeneralOperation", "JobDescription" : "Job for Sales Processing", "JobStartType": "Scheduled", "JobType": "TSQL", "JobCodeText" : "Insert Into Test.TestJobs(SystemName) VALUES ('SalesProcessing')", "JobCodeDatabase": "SSISDBHelper" }, |
The text of all of the jobs will be simply inserting a row into a table that you will find in the SSISDBHelper_CreateScript.SQL file. This SQL file will drop and recreate this database with the code that will be needed to build the complete agent system. I will mostly ignore it for now, but there will be a decent amount of code in there eventually for managing dependencies and reporting on performance, just to name a few purposes. There is a procedure in the script that will allow you to remove old versions of your jobs, by default 2.
In the previous blog, we had already created the procedure that will loop through the Json file and call the agent_CreateJobsFromJson function, but all we had implemented was handling the JobCategory. Now we are going to build the code to create the job.
This starts by defining what the name of the job will look like, and for this I will create a function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
function SQLAgent_FormName ($P_SystemName, $P_SubSystemName, $P_EnvironmentName, $P_JobStartType) { #The name of the agent job is used as the handle to access it in many places. If you want #to use a different form of the name, this is where you would locate it write-host "$NamePrefix-$P_SystemName-$P_SubSystemName-$P_EnvironmentName (Managed)" try { #prefix with TRIGGERED or SCHEDULED, the let the user know which jobs are supposed #to start on their own $NamePrefix = $P_JobStartType.ToUpper() #the (Managed) suffix will be used when we go to delete and recreate jobs in bulk" if ($G_VerboseDetail) { write-host "Defined name $NamePrefix-$P_SystemName-$P_SubSystemName-$P_EnvironmentName (Managed)" } Return("$NamePrefix-$P_SystemName-$P_SubSystemName-$P_EnvironmentName (Managed)") } catch { Write-Error $_ write-host 'Error formatting SQL Agent job name' throw } } |
I prefix the name of the job with TRIGGERED or SCHEDULED, because when you are looking for the starting point, it is good to have an idea where to look. Eventually, the set of triggered jobs is going to become a graph data structure (a directed one, so no cycles, as we definitely don’t want job A to trigger job B to trigger job A), but I find seeing the nodes prefixed with a type that lets me know that it is to have a schedule, or it will start by being started by another job, and later in the process, by a manual process or other methods we might implement.
The suffix part of the name is important to my process (Managed), because I will use this as an indicator that it is part of my managed system, typically to drop all of the jobs before recreating them, in the a future version, I may add this to the defaults as something you can choose. (One tool I will provide later in this series as one of the jobs is a tool to capture job step history, so deleting jobs doesn’t mean you actually lose any history of how long each one took permanently).
I will use this function in the agent_CreateJobsFromJson loop to create the parameter for all of the job creation functions. This following snippet is the loop through the JOBS_Definition.Json file:
|
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 |
$itemsI = $DefinitionItems.Jobs.Count for ($i = 0; $i -lt $itemsI ; $i++) { #fetch the three name parts (if your folder and project names differ, # you can easily add that) $L1_SystemName = $DefinitionItems.Jobs[$i].SystemName $L1_SubsystemName = $DefinitionItems.Jobs[$i].SubsystemName $L1_EnvironmentName = $DefinitionItems.Jobs[$i].EnvironmentName $L1_JobCategory = $DefinitionItems.Jobs[$i].JobCategory $L1_JobStartType = $DefinitionItems.Jobs[$i].JobStartType $L1_JobDescription = $DefinitionItems.Jobs[$i].JobDescription $L1_JobType = $DefinitionItems.Jobs[$i].JobType $L1_JobCodeText = $DefinitionItems.Jobs[$i].JobCodeText $L1_JobCodeDatabase = $DefinitionItems.Jobs[$i].JobcodeDatabase #### Job Category Code Removed for Clarity $L1_AgentJobName = SQLAgent_FormName -P_SystemName $L1_SystemName -P_SubSystemName $L1_SubSystemName ` -P_EnvironmentName $L1_EnvironmentName -P_JobStartType $L1_JobStartType if ($G_VerboseDetail) { Write-Host "Creating Job [$L1_AgentJobName]" } agent_createManagedJobBase -P_ServerName $P_ServerName -P_AgentJobName $L1_AgentJobName ` -P_JobCategory $L1_JobCategory -P_JobDescription $L1_JobDescription #This is where error step will go, first to make it easier to reference ##This is where dependency starting step will go #step to add in the primary job step, depending on the type of job IF ($L1_JobType -eq "TSQL") { agent_addTSQLJobStep -P_ServerName $P_ServerName -P_AgentJobName $L1_AgentJobName ` -P_JobCodeText $L1_JobCodeText -P_JobCodeDatabase $L1_JobCodeDatabase } ##This is where dependency finalize and launch depencencies will go ##This is where emailing upon completion will go |
In this loop, for now, I am implementing 1. Creating the job itself. 2. Adding a TSQL Job step. There are several other pieces of functionality left for later, such as an error step, to send an email to the support team when the process fails, or to launch follow on tasks.
The function to create the job is as follows (Note, some of this code was inspired by code from Joost van Rossum’s blog here: https://microsoft-ssis.blogspot.com/2016/10/using-powershell-to-create-sql-agent.html Definitely the renaming part. I did something like this in my original, T-SQL based job creator, but that was a helpful blog to read as well):
|
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 |
function agent_createManagedJobBase ($P_ServerName, $P_AgentJobName, $P_JobCategory, $P_JobDescription) { #The base Job creation step TRY { #Connect to our SQL Server Instance, using a trusted connection $SQLServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("$P_ServerName") if ($G_VerboseDetail) { write-host "Creating [$P_AgentJobName]" } # Check if job already exists. Then rename for safety $SQLAgentJob = $SQLServer.JobServer.Jobs[$P_AgentJobName] if ($SQLAgentJob) { if ($G_VerboseDetail) { Write-Host "*** Job with name '$AgentJobName' found, renaming and disabling it" } $SQLAgentJob.Rename("z_old_" + $SQLAgentJob.Name + (Get-Date -f MM-dd-yyyy_HH_mm_ss)) $SQLAgentJob.IsEnabled = $false $SQLAgentJob.Alter() } #Create new (empty) job $NewSQLAgentJob = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job ` -argumentlist $SQLServer.JobServer, $P_AgentJobName $NewSQLAgentJob.OwnerLoginName = "SA" #you could change this if you wanted, but this is typically $NewSQLAgentJob.Description = $P_JobDescription #best to have jobs owned by SA $NewSQLAgentJob.Create() #May alter this later, or leave to someone else. $NewSQLAgentJob.ApplyToTargetServer("(local)") #set the job category $NewSQLAgentJob.Category = $P_JobCategory #set the operator to call if the job fails, based on the next settings $OperatorOnCall = environmentvariable_decode('General_OperatorOnCall') $NewSQLAgentJob.EmailLevel = [Microsoft.SqlServer.Management.Smo.Agent.CompletionAction]::OnFailure $NewSQLAgentJob.OperatorToEmail = "$OperatorOnCall"; $NewSQLAgentJob.Alter() if ($G_VerboseDetail) { Write-Host "*** Job '$P_AgentJobName' created" } } catch { Write-Error $_ Write-Host "Something failed creating $P_AgentJobName" Throw; } } |
Finally, the following is the code to add the job step for a T-SQL Job.
|
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 |
function agent_addTSQLJobStep ($P_ServerName, $P_AgentJobName, $P_JobCodeText, $P_JobCodeDatabase) { #Used to create the primary jobsetp when it is a T-SQL job step TRY { if ($G_VerboseDetail){ Write-Host " Creating TSQL:Jobstep for the $P_AgentJobName" } #Connect to the sql server with a trusted connection $SQLServer = New-Object -TypeName Microsoft.SQLServer.Management.Smo.Server("$P_ServerName") #attach to the job we just created $SQLAgentJob = $SQLServer.JobServer.Jobs[$P_AgentJobName] #Add the base job step $JobStepTSQL = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep ` -argumentlist $SQLAgentJob, "Primary Work Step - TSQL" #set the text of the command and the database where it executes $JobStepTSQL.Command = $P_JobCodeText $JobStepTSQL.DatabaseName = $P_JobCodeDatabase #set what occurs on failure. For now, just quit with faiure $JobStepTSQL.OnFailAction = [Microsoft.SqlServer.Management.Smo.Agent.StepCompletionAction]::QuitWithFailure #create the step $JobStepTSQL.Create() if ($G_VerboseDetail){ Write-Host "*** TSQL:Jobstep created for the $P_AgentJobName" } } catch { Write-Error $_ Write-Host "Something failed creating the TSQL Jobstep for the $P_AgentJobName" Throw; } } |
Once you have this code, and have altered the LIBRARY_Variables_DEV.ps1 and LIBRARY_Defaults.ps1 file meet your personal environment needs (something I definitely hope to figure out how to make more seamless later in the process, possibly by making my _DEV file something odd that no one would actually have, and then allowing for an override to Defaults… I am figuring Powershell and writing distributable code out as I go,) you can to TOOL_JobsDeploy_BASE.ps1 and execute it. You should see output that looks something like this:
And opening SSMS, and looking at SQL Agent, something like this:
I have executed this several times when testing, so I have 2 generations. Execute any of the jobs and if you created the database with the script, and set up everything correctly, there will be data in a table.
In the next entry, I will add scheduling to the mix, so you can easily create a set of TSQL jobs, and schedule them to run 1 or more times a day by adding a few lines to a few Json files. From there we will start to gild the lily more and add features like SSIS package scheduling, triggering (this job needs to finish before this job, or these 20 jobs).
Load comments