{"id":86837,"date":"2020-04-05T21:13:13","date_gmt":"2020-04-05T21:13:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86837"},"modified":"2020-04-05T21:15:39","modified_gmt":"2020-04-05T21:15:39","slug":"sql-agent-job-generator-building-the-base-rig-and-managing-categories","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql-agent-job-generator-building-the-base-rig-and-managing-categories\/","title":{"rendered":"SQL Agent Job Generator: Building the Base Rig and Managing Categories"},"content":{"rendered":"

In the previous entry in this series<\/a>, 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:<\/p>\n

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

    The code, as it stands in this blog, can be downloaded here:\u00a0https:\/\/github.com\/drsqlgithub\/SSISDeployTool\/tree\/edbe4c2b13ea77d1a3ac3e4a32ee0561d3e732fd<\/a><\/p>\n

    The file for default configuration details will be named LIBRARY_Defaults.ps1<\/code>, and will start out containing:<\/p>\n

    #When no category is included on the job, use this one\r\n$Global:G_DefaultJobCategory = \"ManagedAgentJob\"\r\n\r\n#display lots of Write-Host messages to help the user see progress\r\n$global:G_VerboseDetail = $True;\r\n\r\n##############################################\r\n#Location of code used in processing\r\n##############################################\r\n\r\n#The template of the shapes used for graphing\r\n$Global:G_VisioTempate = \"$BaseDirectory\\VisioShapes\\SSISDeployShapes.vssx\"\r\n\r\n#The location of SMO being used. \r\n$Global:G_Smo = \"C:\\Program Files\\Microsoft SQL Server\\150\\Setup Bootstrap\\SQL2019\\x64\\Microsoft.SqlServer.Smo.dll\"<\/pre>\n
    You will notice that the Visio template uses back in the first entry on the series on controlling Visio to diagram the jobs<\/a> 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.<\/p>\n
    The next file is TOOL_JobsDeploy_BASE.ps1<\/code>. 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.<\/p>\n
    $Environment = 'DEV' #later, this will be moved to a file named\r\n                     #TOOL_JobsDeploy_DEV.ps1 and Environment will be a param\r\n$BaseDirectory = Get-Location #This script should be portable, so I added the path\r\n                              #dynamically\r\n                              \r\n#get the function scripts\r\n. $BaseDirectory\\LibraryFiles\\LIBRARY_Defaults.ps1\r\n. $BaseDirectory\\LibraryFiles\\LIBRARY_Functions.ps1\r\n. $BaseDirectory\\LibraryFiles\\LIBRARY_Variables_$Environment.ps1\r\n. $BaseDirectory\\LibraryFiles\\LIBRARY_Variables_Global.ps1\r\n\r\n#get the json documents that you will work with\r\n#defines the jobs we have\r\n$DefinitionItemFile = \"$BaseDirectory\\JOBS_Definition.json\"\r\n\r\n#defines the dependencies between jobs\r\n$DependencyItemFile = \"$BaseDirectory\\JOBS_Dependencies.json\" \r\n\r\n#defines the schedules to run jobs\r\n$ScheduleItemFile = \"$BaseDirectory\\JOBS_Schedules.json\"\r\n\r\n#Make sure SMO is in path\r\nAdd-Type -Path \"$G_Smo\"\r\n\r\n#Get the servername from the variable\r\n$ServerName = environmentvariable_decode('SSISServer');\r\n\r\n#call the function \r\nagent_CreateJobsFromJson -P_ServerName $ServerName -P_DefinitionJsonFile $DefinitionItemFile `\r\n                -P_DependencyJsonFile $DependencyItemFile -P_ScheduleJsonFile $ScheduleItemFile<\/pre>\n
    Now, to the LIBRARY_Functions.ps1<\/code> file, I am adding the agent_CreateJobsFromJson<\/code> 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<\/a>, but now we are going to extend the JOBS_Definition.Json<\/code> file by allowing you to define an addition, optional element:<\/p>\n
    {  \r\n   \"Jobs\": [\r\n     {\r\n       \"SystemName\": \"SalesProcessing\",\r\n       \"SubsystemName\": \"_MasterSalesProcessing\",\r\n       \"EnvironmentName\": \"GeneralOperation\",\r\n       \u201cJobCategory\u201d: \u201cCategoryName\u201d <<-- (And no, you can't put comments in Json :))\r\n      },<\/pre>\n
    If the CategoryName is not present, we will use the value defined in $Global:G_DefaultJobCategory<\/code> in the LIBRARY_Defaults.ps1<\/code> 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.<\/p>\n
    function agent_CreateJobsFromJson ($P_ServerName,`\r\n        $P_DefinitionJsonFile, $P_DependencyJsonFile, `\r\n        $P_ScheduleJsonFile){\r\n\r\n    #Open the JSON files\r\n    $DefinitionItems = Get-Content $P_DefinitionJsonFile | ConvertFrom-Json \r\n    $ScheduleItems = Get-Content $P_ScheduleJsonFile | ConvertFrom-Json \r\n    $DependencyItems = Get-Content $P_DependencyJsonFile | ConvertFrom-Json \r\n\r\n    #Loop through the nodes items, and create a node on the diagram\r\n    if ($G_VerboseDetail) {\r\n        Write-Host \"Creating Jobs\"\r\n    }\r\n    $itemsI = $DefinitionItems.Jobs.Count\r\n        \r\n    for ($i = 0; $i -lt $itemsI ; $i++) {\r\n        #fetch the three name parts (if your folder and project names differ,\r\n        #     you can easily add that)\r\n        #$L1_SystemName = $DefinitionItems.Jobs[$i].SystemName\r\n        #$L1_SubsystemName = $DefinitionItems.Jobs[$i].SubsystemName\r\n        #$L1_EnvironmentName = $DefinitionItems.Jobs[$i].EnvironmentName\r\n        $L1_JobCategory = $DefinitionItems.Jobs[$i].JobCategory\r\n        #if JobCategory is not included, use the default\r\n        if (!$L1_JobCategory){\r\n            $L1_JobCategory = $G_DefaultJobCategory;\r\n        }\r\n        if ($G_VerboseDetail) {\r\n            Write-Host \"Handling Job Category: [$L1_JobCategory]\"\r\n        }\r\n        #check for existence\/create category\r\n        agent_maintainCategory -P_AgentServerName $P_ServerName `\r\n                               -P_CategoryName $L1_JobCategory\r\n    }\r\n    \r\n    $itemsI = $DependencyItems.JobDependency.Count\r\n        \r\n    for ($i = 0; $i -lt $itemsI ; $i++) {\r\n        #fetch the three name parts (if your folder and project \r\n        #                         names differ, you can easily add that)\r\n        #$L2_SystemName = $DependencyItems.JobDependency[$i].SystemName\r\n        #$L2_SubsystemName = $DependencyItems.JobDependency[$i].SubsystemName\r\n        #$L2_EnvironmentName = $DependencyItems.JobDependency[$i].EnvironmentName\r\n        \r\n    }\r\n    $itemsI = $ScheduleItems.JobSchedule.Count\r\n        \r\n    for ($i = 0; $i -lt $itemsI ; $i++) {\r\n        #fetch the three name parts (if your folder and project \r\n        #              names differ, you can easily add that)\r\n        #$L2_SystemName = $ScheduleItems.JobSchedule[$i].SystemName\r\n        #$L2_SubsystemName = $ScheduleItems.JobSchedule[$i].SubsystemName\r\n        #$L2_EnvironmentName = $ScheduleItems.JobSchedule[$i].EnvironmentName\r\n    }\r\n}<\/pre>\n
    Finally, the function (agent_maintainCategory<\/code>) 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<\/code> collection, and it seems like you can add an item. But none of the Alter<\/code> methods would make it stick. But just creating a category actually adds it to the JobCategories<\/code> collection too, but in a permanent manner.<\/p>\n
    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!<\/p>\n
    function agent_maintainCategory ($P_AgentServerName, $P_CategoryName) {\r\n    Try {\r\n        #Connect to the SQL Server, you will need to be using a trusted connection here.            \r\n        $ssisServer = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server(\"$P_AgentServerName\") \r\n        \r\n        #variable for the jobserver\r\n        $JobServer = $ssisServer.JobServer\r\n        #grab the job category by name that was passed in\r\n        $Category = $JobServer.JobCategories[\"$P_CategoryName\"] \r\n        #if it wasn't found, add it\r\n        if (!$Category) {\r\n            #create the new category\r\n            $NewCategory = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobCategory')`\r\n                                                     ($JobServer, \"$P_CategoryName\")\r\n            #This was really hard for me. There is a JobCategories collection too... But you add\r\n            #the new Category here.\r\n            $NewCategory.Create()\r\n        \r\n            if ($G_VerboseDetail) {\r\n                Write-Host \"Added category name: $NewCategory\"\r\n            }\r\n        }\r\n    }      \r\n    catch {\r\n        Write-Error $_\r\n        Write-Host \"Something failed handling the category $P_CategoryName\"\r\n        Throw;\r\n    }\r\n}<\/pre>\n
    In the next blog, we will start to generate actual basic T-SQL SQL Agent jobs.<\/p>\n","protected":false},"excerpt":{"rendered":"
    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: A file with defaults for……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[101864],"coauthors":[19684],"class_list":["post-86837","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-powershell-sql-agent"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86837","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=86837"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86837\/revisions"}],"predecessor-version":[{"id":86840,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/86837\/revisions\/86840"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=86837"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=86837"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=86837"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=86837"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}