PASS Data Community Summit logo

PASS Data Community Summit 2023

Get all the latest announcements direct to your inbox

SQL Agent Job Generator: Building the Base Rig and Managing Categories

In the previous entry in this series, I implemented Environment Variables that will let me have one code base that can easily generate jobs in DEV, PROD, or whatever environment you desire. In this entry, we are going to implement the basic building blocks of the process. I will build:

  1. A file with defaults for my configuration
  2. The file to launch the job creation process
  3. A function to create a job, which will call multiple other functions
  4. A function to check for the existence of a job category, and add it if it is new

The code, as it stands in this blog, can be downloaded here: https://github.com/drsqlgithub/SSISDeployTool/tree/edbe4c2b13ea77d1a3ac3e4a32ee0561d3e732fd

The file for default configuration details will be named LIBRARY_Defaults.ps1, and will start out containing:

You will notice that the Visio template uses back in the first entry on the series on controlling Visio to diagram the jobs is in here, the verbose output control, along with two new items we need for our new task. One to make sure that SMO is configured (this being the one from SQL Server 2019 install, while at work I am using 2016 for my SSIS server.) The other to define the default Job Category that we will use if one is not defined explicitly by the job.

The next file is TOOL_JobsDeploy_BASE.ps1. It is the file that has the base details for launching the creation process. The first line will actually be moved out to become a parameter later, but it is much easier to just do this as one file now for building purposes.

Now, to the LIBRARY_Functions.ps1 file, I am adding the agent_CreateJobsFromJson function that I will be growing over time. It is used to loop through the Json files and generate the details that have been defined for our jobs. You can see the basic design in the first entry here, but now we are going to extend the JOBS_Definition.Json file by allowing you to define an addition, optional element:

If the CategoryName is not present, we will use the value defined in $Global:G_DefaultJobCategory in the LIBRARY_Defaults.ps1 file. Note that I have shimmed in the dependency and schedule parts of the code for future steps so we can skip over these details in the future.

Finally, the function (agent_maintainCategory) to add the category that was passed in. Simple enough function, though it took me multiple hours to figure out. There is a JobServer.JobCategories collection, and it seems like you can add an item. But none of the Alter methods would make it stick. But just creating a category actually adds it to the JobCategories collection too, but in a permanent manner.

The lack of documentation/examples of all of these objects that you need to use can get really frustrating, but it is really fun when you actually find the way to make it happen!

In the next blog, we will start to generate actual basic T-SQL SQL Agent jobs.