SQL Agent Job Generator: Creating A Set Of Working Jobs

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.)

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:

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:

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):

Finally, the following is the code to add the job step for a T-SQL Job.

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).