T-SQL Tuesday #130 - Automating Your Stress Away
Approaching the long holiday weekend, I saw that Elizabeth Noble had posted a challenging T-SQL Tuesday post. I’ve always loved automation, and finding novel ways to save time in my day is quite rewarding. There has been one area that has eluded me, and with the sticking points that SQL Agent has around security, it doesn’t lend itself well to automation. If you add availability groups into the mix, the headache soon becomes a dull migraine that never really goes away. My challenge, find a way to source control and automate the deployment of SQL Agent jobs.
I set out with the following criteria for my automation solution:
- A job should be a self-contained unit in source control.
- Some solutions for this problem I had seen used post-deployment scripts that get messy and require complex logic to be handwritten.
- The solution should be able to integrate into an MSBuild task to allow for CI/CD pipelines
- The same deployment solution from source control should be accessible to a developer.
- Use of complex logic within T-SQL would be less accessible
- PowerShell would be more familiar to a developer used to object-oriented code
- Creation of proxies is not in the scope
- Creation of operators is not in the scope
Ultimately, I settled on creating a wrapper script around a series of functions from the dbatools module. A SQL agent job consists of both Schedules and Steps, both of which can contain one or more objects. While you can create a job through SSMS without steps or schedules, this doesn’t provide much functionality. Through the dbatools functions, this results in an agent job that remains disabled. You can also assign a job to a category.
When developing an automation routine like this, my approach is to break the work down into the most fundamental components. Once I have these, I establish that I can get each of these components functional on their own. Running through each element allows me to identify interdependencies that may exist. I knew that one of my main requirements would be a single file that could contain all information that defined a job.
Knowing that I wanted to create a file to define a job, I also wanted to ensure that the file definition would stay relatively static through the development. To achieve this, I would need to know what functions within dbatools I would use for my automation. I knew that Jobs, Schedules, Steps, and Categories would require creation or modification, so I needed to identify to corresponding functions within dbatools that could build or modify a job or related items. The discovered objects for this were:
- Get-DbaAgentCategory
- Get-DbaAgentJob
- Get-DbaAgentJobStep
- Get-DbaAgentSchedule
- New-DbaAgentCategory
- New-DbaAgentJob
- New-DbaAgentJobStep
- New-DbaAgentSchedule
- Remove-DbaAgentJobStep
- Set-DbaAgentCategory
- Set-DbaAgentJob
- Set-DbaAgentJobStep
- Set-DbaAgentSchedule
Once I had identified the various elements of a job (Job, Step, Schedule, and Category), I envisioned a file format that could store the data, and PowerShell could query. JSON and XML were considered, with JSON garnering initial favor due to the development audience that would be using the automation. XML ultimately was favored due to the specification allowing comments, where JSON does not allow this by design. PowerShell can effortlessly parse XML, and the syntax for the queries is intuitive. When designing the XML file, I intentionally kept the property names in alignment with the corresponding nodes function parameters. There are some exceptions, the Name property on each node, the Id node within the Step node, and the Value property within FrequencyInterval and Flag nodes.
An example XML document:
<?xml version="1.0" encoding="utf-8"?>
<!--Reference kb for sp_add_job and dbatools.io command New-DbaAgentJob for translation of paramaters -->
<Job Name="Multi-Step Job" Disabled="0" Description="" StartStepId="1" Category="My Category" OwnerLogin="sa" EventLogLevel="2" EmailLevel="0" PageLevel="0" EmailOperator="" NetsendOperator="" PageOperator="" DeleteLevel="0">
<!-- A job can have multiple steps -->
<Steps>
<!-- Reference kb for sp_add_jobstep and dbatools.io command New-DbaAgentJobStep for translation of paramaters. -->
<Step Id="1" Name="Step 1" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 1" CmdExecSuccessCode="" OnSuccessAction="GoToStep" OnSuccessStepId="3" OnFailAction="GoToStep" OnFailStepId="2" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
<Step Id="2" Name="Step 2 (Recover)" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 2" CmdExecSuccessCode="" OnSuccessAction="GoToStep" OnSuccessStepId="3" OnFailAction="QuitWithFailure" OnFailStepId="" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
<Step Id="3" Name="Step 3" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 3" CmdExecSuccessCode="" OnSuccessAction="GoToStep" OnSuccessStepId="5" OnFailAction="GoToStep" OnFailStepId="4" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
<Step Id="4" Name="Step 4 (Recover)" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 4" CmdExecSuccessCode="" OnSuccessAction="GoToStep" OnSuccessStepId="5" OnFailAction="QuitWithFailure" OnFailStepId="" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
<Step Id="5" Name="Step 5" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 5" CmdExecSuccessCode="" OnSuccessAction="GoToNextStep" OnSuccessStepId="" OnFailAction="QuitWithFailure" OnFailStepId="" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
<Step Id="6" Name="Step 6 (Finalize)" Subsystem="TransactSql" SubsystemServer="" Command="SELECT 6" CmdExecSuccessCode="" OnSuccessAction="QuitWithSuccess" OnSuccessStepId="" OnFailAction="QuitWithFailure" OnFailStepId="" Database="master" DatabaseUser="" RetryAttempts="0" RetryInterval="0" OutputFileName="" ProxyName="">
<Flags>
<Flag Value="AppendToTableLog"/>
<Flag Value="AppendToJobHistory"/>
</Flags>
</Step>
</Steps>
<!--A job can be attached to multiple schedules -->
<Schedules>
<!--Reference kb for sp_add_schedule and dbatools.io command New-DbaAgentSchedule for translation of parameters.-->
<Schedule Name="Test Schedule" Disabled="1" FrequencyType="Daily" FrequencySubdayType="Time" FrequencySubdayInterval="0" FrequencyRelativeInterval="" FrequencyRecurrenceFactor="1" StartDate="20200903" EndDate="99991231" StartTime="030000" EndTime="000000">
<FrequencyIntervals>
<FrequencyInterval Value="31"/>
</FrequencyIntervals>
</Schedule>
<Schedule Name="Alternate Test Schedule" Disabled="0" FrequencyType="Monthly" FrequencySubdayType="Time" FrequencySubdayInterval="0" FrequencyRelativeInterval="" FrequencyRecurrenceFactor="1" StartDate="20200903" EndDate="99991231" StartTime="030000" EndTime="000000">
<FrequencyIntervals>
<FrequencyInterval Value="2"/>
</FrequencyIntervals>
</Schedule>
<Schedule Name="Additional Test Schedule" Disabled="0" FrequencyType="MonthlyRelative" FrequencySubdayType="Time" FrequencySubdayInterval="0" FrequencyRelativeInterval="Third" FrequencyRecurrenceFactor="1" StartDate="20200903" EndDate="99991231" StartTime="030000" EndTime="000000">
<FrequencyIntervals>
<FrequencyInterval Value="Sunday"/>
</FrequencyIntervals>
</Schedule>
</Schedules>
</Job>
With the functions needed and a file specification in hand, I needed to begin laying down a framework for the script. I started with handling creating a job that didn’t exist as this felt like the easier path–looking at the structure of how SSMS scripted out a job, I observed a distinct pattern. A transaction is always at the very beginning of the batch. The check of a job category and subsequent creation follows the transaction opening. Next, job creation with each step creation following in order. The job must then update the starting step; schedule creation occurs last. Each command checks for errors, and if found, a rollback occurs.
What I discovered using the functions was that unlike a T-SQL job creation where the whole job creation is an atomic unit, each function call is a single, smaller transaction. Smaller transactions meant that I had added some complexity that I would need to handle. A few of the snags I hit were in job step creation, job creation, and schedule creation.
The logic that I landed on to handle the smaller transactions in the functions is below.
During the process of creating my wrapper script and related debugging activities, I identified a few minor bugs in some of the dbatools functions. Early on, when I was trying to match a step configuration to an existing job in a test environment, one of the dbatools functions did not seem to respect a value that the help documentation indicated that should be allowed. The multiple layers of debugging added another layer of complexity and challenge to the automation. With many challenges overcome, seeing the routine generally working and narrowing down the debugging to a few lingering issues was exciting. Seeing a static file being turned into a job on a server and realizing that this was because of the couple hundred lines of code that did not exist just a few days prior was gratifying. There are a few additional to-do items in this automation routine, but this is far closer to reality than it was. Some of the things I have in mind are:
- Check for the proxy existence, throw an error if it isn’t there
- Check for operator existence, throw an error if it isn’t there
- Create an XML export wrapper function for existing jobs
- Using XML export wrapper and XML file, perform hash compare, throw an error and disable job if mismatch happens
Here is my wrapper script as it stands today:
$SQLInstances = @("localhost") # For safety purposes, each instance should be transactionally seperated in insulated loop.
[xml]$Job = Get-Content -Path C:\Temp\MyJob.xml
foreach ($SQLInstance in $SQLInstances) {
# Grab our schedules
$Schedules = $Job.SelectNodes("//Schedule")
# Grab our steps
$Steps = $Job.SelectNodes("//Step") | Sort-Object { $_.Id }
if ($null -eq (Get-DbaAgentJob -SqlInstance $SQLInstance -Job $Job.Job.Name)) {
# Create the Category if it doesn't exist
if ($null -eq (Get-DbaAgentJobCategory -SqlInstance $SQLInstance -Category $Job.Job.Category)) {
"Creating Category $($Job.Job.Category)"
New-DbaAgentJobCategory -SqlInstance $SQLInstance -Category $Job.Job.Category
}
# Create a stub job with valid schedules attached already
"Creating Job $($Job.Job.Name)"
New-DbaAgentJob -SqlInstance $SQLInstance -Job $Job.Job.Name -Schedule (Get-DbaAgentSchedule -SqlInstance $SQLInstance -Schedule $Schedules.Name).Name -Disabled
#Build our schedules
foreach ($Schedule in $Schedules) {
$ScheduleParam = @{
SQLInstance = $SQLInstance
Schedule = $Schedule.Name
Job = $Job.Job.Name
FrequencyInterval = $Schedule.FrequencyIntervals.FrequencyInterval.Value
FrequencyType = $Schedule.FrequencyType
FrequencyRecurrenceFactor = $Schedule.FrequencyRecurrenceFactor
FrequencySubdayType = $Schedule.FrequencySubdayType
FrequencySubdayInterval = $Schedule.FrequencySubdayInterval
StartDate = $Schedule.StartDate
StartTime = $Schedule.StartTime
EndDate = $Schedule.EndDate
EndTime = $Schedule.EndTime
}
if ($null -eq (Get-DbaAgentSchedule -SqlInstance $SQLInstance -Schedule $Schedule.Name)) {
if ($Schedule.Disabled -eq "0") {
"Creating $($Job.Job.Name): Schedule $($Schedule.Name), Enabled"
if ("" -eq $Schedule.FrequencyRelativeInterval) {
New-DbaAgentSchedule @ScheduleParam
}
else {
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
New-DbaAgentSchedule @ScheduleParam
}
}
else {
"Creating $($Job.Job.Name): Schedule $($Schedule.Name), Disabled"
$ScheduleParam.Add("Disabled",$true)
if ("" -eq $Schedule.FrequencyRelativeInterval) {
New-DbaAgentSchedule @ScheduleParam
}
else {
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
New-DbaAgentSchedule @ScheduleParam
}
}
}
else {
# Need to set the schedules, properties could be changing and existing schedule
# If this happens, the schedule WILL NOT update. (Re-)attach the schedule at the end.
if ($Schedule.Disabled -eq "0") {
"Updating $($Job.Job.Name): Schedule $($Schedule.Name), Enabled"
if ("" -eq $Schedule.FrequencyRelativeInterval) {
Set-DbaAgentSchedule @ScheduleParam
}
else {
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
Set-DbaAgentSchedule @ScheduleParam
}
}
else {
"Creating $($Job.Job.Name): Schedule $($Schedule.Name), Disabled"
$ScheduleParam.Add("Disabled",$true)
if ("" -eq $Schedule.FrequencyRelativeInterval) {
Set-DbaAgentSchedule @ScheduleParam
}
else {
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
Set-DbaAgentSchedule @ScheduleParam
}
}
}
}
# first pass of steps, will need to run a set after to handle interleaving
foreach ($Step in $Steps) {
"Creating Steps For Job $($Job.Job.Name) [STEP]: $($Step.Name)."
New-DbaAgentJobStep -SqlInstance $SQLInstance `
-Job $Job.Job.Name `
-StepId $Step.Id `
-StepName $Step.Name `
-Subsystem $Step.Subsystem `
-SubsystemServer $Step.SubsystemServer `
-Command $Step.Command `
-CmdExecSuccessCode $Step.CmdExecSuccessCode `
-Database $Step.Database `
-DatabaseUser $Step.DatabaseUser `
-RetryAttempts $Step.RetryAttempts `
-RetryInterval $Step.RetryInterval `
-OutputFileName $Step.OutputFileName `
-Flag $Step.Flags.Flag.Value `
-ProxyName $Step.ProxyName
}
# second pass of steps, to handle interleaving
foreach ($Step in $Steps) {
"Finalizing Steps For Job $($Job.Job.Name) [STEP]: $($Step.Name). Adding Success/Fail Actions."
Set-DbaAgentJobStep -SqlInstance $SQLInstance `
-Job $Job.Job.Name
-StepName $Step.Name `
-Subsystem $Step.Subsystem `
-SubsystemServer $Step.SubsystemServer `
-Command $Step.Command `
-CmdExecSuccessCode $Step.CmdExecSuccessCode `
-OnSuccessAction $Step.OnSuccessAction `
-OnSuccessStepId $Step.OnSuccessStepId `
-OnFailAction $Step.OnFailAction `
-OnFailStepId $Step.OnFailStepId `
-Database $Step.Database `
-DatabaseUser $Step.DatabaseUser `
-RetryAttempts $Step.RetryAttempts `
-RetryInterval $Step.RetryInterval `
-OutputFileName $Step.OutputFileName `
-Flag $Step.Flags.Flag.Value `
-ProxyName $Step.ProxyName
}
$JobParam = @{
SQLInstance = $SQLInstance
Job = $Job.Job.Name
Schedule = $Schedules.Name
Description = $Job.Job.Description
StartStepId = $Job.Job.StartStepId
Category = $Job.Job.Category
OwnerLogin = $Job.Job.OwnerLogin
EventLogLevel = $Job.Job.EventLogLevel
EmailLevel = $Job.Job.EmailLevel
PageLevel = $Job.Job.PageLevel
EmailOperator = $Job.Job.EmailOperator
NetsendOperator = $Job.Job.NetsentOperator
PageOperator = $Job.Job.PageOperator
DeleteLevel = $Job.Job.DeleteLevel
}
# Finish the job creation
if ($Job.Job.Disabled -eq 0) {
$JobParam.Add("Enabled",$true)
"Enable Job [$($Job.Job.Name)], set remaining properties"
Set-DbaAgentJob @JobParam
}
else {
$JobParam.Add("Disabled",$true)
"Disable Job [$($Job.Job.Name)], set remaining properties"
Set-DbaAgentJob @JobParam
}
}
else {
# Check for a category update
if ((Get-DbaAgentJob -SqlInstance $SQLInstance -Job $Job.Job.Name).Category -ne $Job.Job.Category)
{
if($null -ne (Get-DbaAgentJobCategory -SqlInstance $SQLInstance -Category $Job.Job.Category))
{
"Existing Job $($Job.Job.Name): Updating to Existing Category: $($Job.Job.Category)."
Set-DbaAgentJob -SqlInstance $SQLInstance -Job $Job.Job.Name -Category $Job.Job.Category
}
else {
"Existing Job $($Job.Job.Name): Creating New Category: $($Job.Job.Category)."
New-DbaAgentJobCategory -SqlInstance $SQLInstance -Category $Job.Job.Category
}
}
#Build our schedules
foreach ($Schedule in $Schedules) {
# Check to see if the schedule exists (New vs. Set)
"Existing Job $($Job.Job.Name): Checking Schedule: $($Schedule.Name)."
$ScheduleParam = @{
SQLInstance = $SQLInstance
Schedule = $Schedule.Name
Job = $Job.Job.Name
FrequencyInterval = $Schedule.FrequencyIntervals.FrequencyInterval.Value
FrequencyType = $Schedule.FrequencyType
FrequencyRecurrenceFactor = $Schedule.FrequencyRecurrenceFactor
FrequencySubdayType = $Schedule.FrequencySubdayType
FrequencySubdayInterval = $Schedule.FrequencySubdayInterval
StartDate = $Schedule.StartDate
StartTime = $Schedule.StartTime
EndDate = $Schedule.EndDate
EndTime = $Schedule.EndTime
}
if($null -eq (Get-DbaAgentSchedule -SqlInstance $SQLInstance -Schedule $Schedule.Name)) {
"Unable to locate schedule: $($Schedule.Name), Creating New Schedule."
# Check to see what parameter splat (Enable/Disable,FrequencyRelativeInterval) we need to use (potential refactor)
if ($Schedule.Disabled -eq "0") {
if ("" -eq $Schedule.FrequencyRelativeInterval) {
"Creating Schedule ($($Schedule.Name)): Enabled, relative interval NOT needed"
New-DbaAgentSchedule @ScheduleParam
}
else {
"Creating Schedule ($($Schedule.Name)): Enabled, relative interval needed"
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
New-DbaAgentSchedule @ScheduleParam
}
}
else {
$ScheduleParam.Add("Disabled",$true)
if ("" -eq $Schedule.FrequencyRelativeInterval) {
"Creating Schedule ($($Schedule.Name)): DISABLED, relative interval NOT needed"
New-DbaAgentSchedule @ScheduleParam
}
else {
"Creating Schedule ($($Schedule.Name)): DISABLED, relative interval needed"
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
New-DbaAgentSchedule @ScheduleParam
}
}
}
else {
"Found Schedule: $($Schedule.Name), Updating."
# Need to set the schedules, properties could be changing and existing schedule
# If this happens, the schedule WILL NOT update. (Re-)attach the schedule at the end.
# Check to see what parameter splat (Enable/Disable,FrequencyRelativeInterval) we need to use (potential refactor)
if ($Schedule.Disabled -eq "0") {
if ("" -eq $Schedule.FrequencyRelativeInterval) {
"Altering Schedule ($($Schedule.Name)): Enabled, relative interval NOT needed"
Set-DbaAgentSchedule @ScheduleParam
}
else {
"Altering Schedule ($($Schedule.Name)): Enabled, relative interval needed"
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
Set-DbaAgentSchedule @ScheduleParam
}
}
else {
$ScheduleParam.Add("Disabled",$true)
if ("" -eq $Schedule.FrequencyRelativeInterval) {
"Altering Schedule ($($Schedule.Name)): DISABLED, relative interval NOT needed"
Set-DbaAgentSchedule @ScheduleParam
}
else {
"Altering Schedule ($($Schedule.Name)): DISABLED, relative interval needed"
$ScheduleParam.Add("FrequencyRelativeInterval",$Schedule.FrequencyRelativeInterval)
Set-DbaAgentSchedule @ScheduleParam
}
}
}
}
# Check for job step order (ID changes), name changes, and step removals.
# Need to remove and re-add if this occurs for simplification
$JobSteps = (Get-DbaAgentJobStep -SqlInstance $SqlInstance -Job $Job.Job.Name) | Sort-Object $_.ID
$i = 0
$j = ($Steps | Measure-Object).Count - 1
$RemoveSteps = $false
if($null -ne $JobSteps)
{
"Existing Job $($Job.Job.Name): Checking for Step ID OR Name Changes."
while ($i -le $j) {
if (($JobSteps[$i].ID -ne $Steps[$i].Id) -or ($JobSteps[$i].ID -eq $Steps[$i].Id -and $JobSteps[$i].Name -ne $Steps[$i].Name)) {
"Checking Step ID ($($JobSteps[$i].ID)):$($JobSteps[$i].Name) against XML Step ID($($Steps[$i].ID)):$($Steps[$i].Name)."
$RemoveSteps = $true
# We can stop our checks after the first hit.
break
}
$i++
}
}
else {
"No Steps Found. Bypass Checks."
$RemoveSteps = $true
}
# If we found step ID changes, remove all of the job steps. Easier than nested loops.
if ($true -eq $RemoveSteps) {
"Found Step ID or Step Name mismatches. Removing Current Job Steps to Allow Recreation."
foreach ($JobStep in $JobSteps) {
"Removing Steps From $($Job.Job.Name) [STEP]: $($Step.Name)."
Remove-DbaAgentJobStep -SqlInstance $SqlInstance `
-Job $Job.Job.Name `
-StepName $JobStep.Name
}
# first pass of steps, will need to run a set after to handle interleaving
foreach ($Step in $Steps) {
"Creating Steps For Job $($Job.Job.Name) [STEP]: $($Step.Name)."
New-DbaAgentJobStep -SqlInstance $SQLInstance `
-Job $Job.Job.Name `
-StepId $Step.Id `
-StepName $Step.Name `
-Subsystem $Step.Subsystem `
-SubsystemServer $Step.SubsystemServer `
-Command $Step.Command `
-CmdExecSuccessCode $Step.CmdExecSuccessCode `
-Database $Step.Database `
-DatabaseUser $Step.DatabaseUser `
-RetryAttempts $Step.RetryAttempts `
-RetryInterval $Step.RetryInterval `
-OutputFileName $Step.OutputFileName `
-Flag $Step.Flags.Flag.Value `
-ProxyName $Step.ProxyName
}
# second pass of steps, to handle interleaving
foreach ($Step in $Steps) {
"Finalizing Steps For Job $($Job.Job.Name) [STEP]: $($Step.Name). Adding Success/Fail Actions."
Set-DbaAgentJobStep -SqlInstance $SQLInstance `
-Job $Job.Job.Name `
-StepName $Step.Name `
-Subsystem $Step.Subsystem `
-SubsystemServer $Step.SubsystemServer `
-Command $Step.Command `
-CmdExecSuccessCode $Step.CmdExecSuccessCode `
-OnSuccessAction $Step.OnSuccessAction `
-OnSuccessStepId $Step.OnSuccessStepId `
-OnFailAction $Step.OnFailAction `
-OnFailStepId $Step.OnFailStepId `
-Database $Step.Database `
-DatabaseUser $Step.DatabaseUser `
-RetryAttempts $Step.RetryAttempts `
-RetryInterval $Step.RetryInterval `
-OutputFileName $Step.OutputFileName `
-Flag $Step.Flags.Flag.Value `
-ProxyName $Step.ProxyName
}
}
else {
# Re-cache our job steps for altering the existing steps
"No Step Order or Step Name Changes Found. Altering Existing Job Steps."
$JobSteps = (Get-DbaAgentJobStep -SqlInstance $SqlInstance -Job $Job.Job.Name) | Sort-Object $_.ID
$i = 0
$j = ($Steps | Measure-Object).Count - 1
while ($i -le $j) {
"Altering $($Job.Job.Name) [STEP]: $($Steps[$i].Name)."
Set-DbaAgentJobStep -SqlInstance $SqlInstance `
-Job $Job.Job.Name `
-StepName $JobSteps[$i].Name `
-NewName $Steps[$i].Name `
-Subsystem $Steps[$i].Subsystem `
-SubsystemServer $Steps[$i].SubsystemServer `
-Command $Steps[$i].Command `
-CmdExecSuccessCode $Steps[$i].CmdExecSuccessCode `
-OnSuccessAction $Steps[$i].OnSuccessAction `
-OnSuccessStepId $Steps[$i].OnSuccessStepId `
-OnFailAction $Steps[$i].OnFailAction `
-OnFailStepId $Steps[$i].OnFailStepId `
-Database $Steps[$i].Database `
-DatabaseUser $Steps[$i].DatabaseUser `
-RetryAttempts $Steps[$i].RetryAttempts `
-RetryInterval $Steps[$i].RetryInterval `
-OutputFileName $Steps[$i].OutputFileName `
-Flag $Steps[$i].Flags.Flag.Value `
-ProxyName $Steps[$i].ProxyName
$i++
}
$i = ($JobSteps | Measure-Object).Count
while ($i -lt $j) {
"Adding New Step From XML StepID($($Steps.Id)):$($Steps.Name)"
New-DbaAgentJobStep -SqlInstance $SqlInstance `
-Job $Job.Job.Name `
-StepId $Steps[$i].Id `
-StepName $Steps[$i].Name `
-Subsystem $Steps[$i].Subsystem `
-SubsystemServer $Steps[$i].SubsystemServer `
-Command $Steps[$i].Command `
-CmdExecSuccessCode $Steps[$i].CmdExecSuccessCode `
-OnSuccessAction $Steps[$i].OnSuccessAction `
-OnSuccessStepId $Steps[$i].OnSuccessStepId `
-OnFailAction $Steps[$i].OnFailAction `
-OnFailStepId $Steps[$i].OnFailStepId `
-Database $Steps[$i].Database `
-DatabaseUser $Steps[$i].DatabaseUser `
-RetryAttempts $Steps[$i].RetryAttempts `
-RetryInterval $Steps[$i].RetryInterval `
-OutputFileName $Steps[$i].OutputFileName `
-Flag $Steps[$i].Flags.Flag.Value `
-ProxyName $Steps[$i].ProxyName
$i++
}
}
$JobParam = @{
SQLInstance = $SQLInstance
Job = $Job.Job.Name
Schedule = $Schedules.Name
Description = $Job.Job.Description
StartStepId = $Job.Job.StartStepId
Category = $Job.Job.Category
OwnerLogin = $Job.Job.OwnerLogin
EventLogLevel = $Job.Job.EventLogLevel
EmailLevel = $Job.Job.EmailLevel
PageLevel = $Job.Job.PageLevel
EmailOperator = $Job.Job.EmailOperator
NetsendOperator = $Job.Job.NetsentOperator
PageOperator = $Job.Job.PageOperator
DeleteLevel = $Job.Job.DeleteLevel
}
# Finish the job alteration
if ($Job.Job.Disabled -eq 0) {
$JobParam.Add("Enabled",$true)
"Enable Job [$($Job.Job.Name)], set remaining properties"
Set-DbaAgentJob @JobParam
}
else {
$JobParam.Add("Disabled",$true)
"Disable Job [$($Job.Job.Name)], set remaining properties"
Set-DbaAgentJob @JobParam
}
}
}