functions/SetJobSteps.ps1
|
Function Set-JobSteps { <# .Synopsis Delete steps of a SQL Agent Job and re-create. .Description Steps for SQL Agent Job will be deleted and re-created to match the settings in the xml file. .Parameter sqlServer The SQL Connection that SQL Agent Job is on. .Parameter root The XML Object .Example $SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;" $JobManifestXmlFile = "C:\Reports\Our_First_Job.xml" $SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString [xml] $_xml = [xml] (Get-Content -Path $JobManifestXmlFile) $x = Get-Xml -XmlFile $_xml $sqlAgentJob = Set-Job -SqlServer $SqlConnection -root $x Set-JobSteps -SqlServer $SqlConnection -root $x -job $SqlAgentJob Disconnect-SqlConnection -SqlDisconnect $SqlConnection #> [CmdletBinding()] param ( [Microsoft.SqlServer.Management.Smo.SqlSmoObject] [ValidateNotNullorEmpty()] $SqlServer, [System.Xml.XmlLinkedNode] [ValidateNotNullorEmpty()] $root, [Microsoft.SqlServer.Management.Smo.Agent.AgentObjectBase] [ValidateNotNullorEmpty()] $job ) #clear steps and always re-create $job.RemoveAllJobSteps() $job.Refresh() #re-create steps; defaults for properties are created at first $Steps = $root.Steps foreach ($step in $Steps.ChildNodes) { try { $StepName = $step.Name $JobStep = New-Object ('Microsoft.SqlServer.Management.Smo.Agent.JobStep') ($job, $StepName) $JobStep.Create() Write-Verbose "Blank step $StepName Created" -Verbose $job.Refresh() } catch { throw $_.Exception } finally { if ($null -ne $JobStep) { Remove-Variable -Name "JobStep" } } } foreach ($step in $Steps.ChildNodes) { #Question: huh? Why re-enter the loop? #Answer: We need to create all job steps up front so that we can set on success or on fail accordingly #eg if step succeeds and user wants to use "GoToStep" on Action, then have to get ID of step they want to go to from "OnSuccessStep" #same applies to Failures { try { $JobStep_Properties = $job.JobSteps | Where-Object {$_.Name -eq $step.Name} $StepName = $Step.Name $JobStep_Properties.SubSystem = $step.SubSystem if ($Step.RunAs) { $RunAs = $Step.RunAs.Include if (Test-Path variable:$RunAs) { [string]$value = Get-Variable $RunAs -ValueOnly Write-Verbose ('Setting variable: {0} = {1}' -f $update, $value) -Verbose foreach ($element in $step.SelectNodes("/Job/Steps/Step/RunAs") | Where-Object {$_.Include -eq $RunAs}) { $element.Name = $value } } else { throw ('RunAs Account is not set in the current scope for step {0} (but are defined in the xml): {1}' -f $StepName, $RunAs.Include) } $JobStep_Properties.ProxyName = $Step.RunAs.Name } if ($step.OnSuccessAction -eq "GoToStep") { $OnSuccessGoToStepId = Get-GoToStepId -JobObject $job -GoToJobStep $step.OnSuccessStep $JobStep_Properties.OnSuccessAction = $step.OnSuccessAction $JobStep_Properties.OnSuccessStep = $OnSuccessGoToStepId } else { $JobStep_Properties.OnSuccessAction = $step.OnSuccessAction } if ($step.OnFailAction -eq "GoToStep") { $OnFailGoToStepId = Get-GoToStepId -JobObject $job -GoToJobStep $step.OnFailStep $JobStep_Properties.OnFailAction = $step.OnFailAction $JobStep_Properties.OnFailStep = $OnFailGoToStepId } else { $JobStep_Properties.OnFailAction = $step.OnFailAction } $JobStep_Properties.RetryAttempts = $step.RetryAttempts $JobStep_Properties.RetryInterval = $step.RetryInterval if ($step.SubSystem -eq "Ssis") { Write-Verbose "Setting SSIS Server for step." $keys = $($step.SsisServer) foreach ($var in $keys) { $update = $var.Include if (Test-Path variable:$update) { [string]$value = Get-Variable $update -ValueOnly Write-Verbose ('Setting variable: {0} = {1}' -f $update, $value) -Verbose foreach ($element in $step.SelectNodes("/Job/Steps/Step/SsisServer") | Where-Object {$_.Include -eq $update}) { $element.Name = $value } } else { $missingVariables += $update } } if ($missingVariables.Count -gt 0) { throw ('Ssis Server is not set in the current scope (but are defined in the xml): {0}' -f ($missingVariables -join " `n")) } $thisSsisServer = $step.SsisServer.Name $step = Set-IscVariables -SsisStep $Step Write-Verbose "Step $stepName is a Ssis step. Connecting to SSIS Instance to verify the catalog exists. If it does will assume everything else required exists." -Verbose $script = "SELECT 'exists' FROM ssisdb.CATALOG.folders folder INNER JOIN ssisdb.CATALOG.projects project on project.folder_id = folder.folder_id WHERE folder.NAME = '$($Step.SsisServerDetails.SsisServerCatalogFolder)' AND project.name = '$($Step.SsisServerDetails.SsisServerCatalogProject)'" $checkSsisExists = $SqlServer.ConnectionContext.ExecuteScalar($script) # if ($checkSsisExists -ne "exists") { # $msg = "Either Folder " + $Step.SsisServerDetails.SsisServerCatalogFolder + " or Project " + $Step.SsisServerDetails.SsisServerCatalogProject + " does not exist. Cannot fill variables for ssisCommand correctly. Please deploy all SSIS Projects first " # Write-Verbose $msg -Verbose # Throw $msg; # } $script = "SELECT er.reference_id FROM ssisdb.CATALOG.environment_references er INNER JOIN ssisdb.CATALOG.projects p ON p.project_id = er.project_id WHERE er.environment_name = '$($Step.SsisServerDetails.SsisServerCatalogEnvironment)' AND p.NAME = '$($Step.SsisServerDetails.SsisServerCatalogProject)'" Write-Host $script try { $environmentReference = $SqlServer.ConnectionContext.ExecuteScalar($script) } catch { throw $_.Exception } try { $ssisCommand = @" /ISSERVER "\"\SSISDB\$($Step.SsisServerDetails.SsisServerCatalogFolder)\$($Step.SsisServerDetails.SsisServerCatalogProject)\$($Step.SsisServerDetails.SsisServerCatalogPackage)\"" /SERVER "$($thisSsisServer)" /ENVREFERENCE $environmentReference /Par "\"`$ServerOption::LOGGING_LEVEL(Int16)\"";1 /Par "\"`$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E "@ Write-Host "This is the ssiscommand" Write-Host $ssisCommand $JobStep_Properties.Command = $ssisCommand } catch { throw $_.Exception } } else { $JobStep_Properties.Command = $step.Command } $JobStep_Properties.Alter() $JobStep_Properties.Refresh() Write-Verbose "Successfully Updated properties for Step $StepName." -Verbose } catch { throw $_.Exception } finally { if ($null -ne $JobStep) { Remove-Variable -Name "JobStep" Remove-Variable -Name "JobStep_Properties" } } } } |