functions/GetSqlAgentAsXml.ps1

Function Get-SqlAgentAsXml {
    <#
.Synopsis
Takes a SQL Agent Job that exists on a SQL Instance and converts into XML
.Description
Rather than trying to create XML files that represent SQL Agent Jobs from scratch, this function aims to take all jobs that already exists on an instance and convert into
an XML file that can be used by the rest of the module to import job/schedules/steps etc.
 
Not intended to be used in an automated process, but rather a function designed to help teams adopt the whole automate SQl Agent as XML files quicker.
.Parameter SqlServer
SQL Server Instance that has Agent Job on it.
.Parameter filePath
Folder directory where we want to create the XML files.
.Parameter dateFormat
Date Format that the SQL Server Instance uses: some might be EN-GB, some EN-US.
.Parameter jobName
Optional parameter. Name of SQL Agent Job that we want to export to XML.
If not included all SQL Agent Jobs will be exported, except for the following:
     -SSIS Server Maintenance Job
    - syspolicy_purge_history
.Example
 $SqlConnectionString = "data source = .; initial catalog = master; trusted_connection = true;"
 $SqlConnection = Connect-SqlConnection -ConnectionString $SqlConnectionString
 Get-SqlAgentAsXml -SqlServer $SqlConnection -filePath "C:\Reports" -dateFormat 'MM/dd/yyyy'
 Get-SqlAgentAsXml -SqlServer $SqlConnection -filePath "C:\Reports" -dateFormat 'MM/dd/yyyy' -JobName "My SQL Agent Job"
 Disconnect-SqlConnection -SqlDisconnect $SqlConnection
#>

    [CmdletBinding()]
    param
    (
        [Microsoft.SqlServer.Management.Smo.SqlSmoObject]
        [ValidateNotNullorEmpty()]
        $SqlServer,
        [string]
        $JobName,
        [String]
        [ValidateNotNullorEmpty()]
        $filePath
    )
    if ($jobName) {
        $serverJobs = $sqlserver.JobServer.Jobs |where-object {$_.Name -like $jobName}
    }
    else {
        $serverJobs = $sqlserver.JobServer.Jobs |where-object {$_.Name -notmatch 'SSIS Server Maintenance Job|syspolicy_purge_history'}

    }
    foreach ($job in $serverJobs) {
        $pattern = '[^a-zA-Z0-9_]'
        Write-Verbose "Removing non-alphanumeric chars from job name, so that we can use job name as file name." -Verbose
        $FormattedJobName = $Job.Name -replace $pattern, ""
        Write-Verbose "The job name is $($job.name)" -Verbose
        $FileOutput = Join-Path $filePath "$($FormattedJobName).xml"
        Write-Verbose "File output - $FileOutput" -Verbose
        # Create The Document
        $XmlWriter = New-Object System.XMl.XmlTextWriter($FileOutput, $Null)
        # Set The Formatting
        $xmlWriter.Formatting = "Indented"
        $xmlWriter.Indentation = "4"
        # Write the XML Decleration
        $xmlWriter.WriteStartDocument()
        # Write Root Element
        $xmlWriter.WriteStartElement("Job")
        # Write the Document
        $xmlWriter.WriteElementString("Name", "$($Job.Name)")
        $XmlWriter.WriteElementString("Owner", "$($Job.OwnerLoginName)")
        $xmlWriter.WriteElementString("Description", "$($Job.Description)")
        $xmlWriter.WriteElementString("Enabled", "$($Job.IsEnabled)")
        $xmlWriter.WriteStartElement("Category")
        $xmlWriter.WriteAttributeString("Include", "ServerJobCategory")
        $xmlWriter.WriteElementString("Value", "$($Job.Category)")
        $xmlWriter.WriteEndElement()
        $xmlWriter.WriteStartElement("TargetServers")
        $xmlWriter.WriteStartElement("TargetServer")
        $xmlWriter.WriteAttributeString("Include", "$($job.EnumTargetServers().ServerName)")
        $xmlWriter.WriteElementString("Value", "$($job.EnumTargetServers().ServerName)")
        $xmlWriter.WriteEndElement()
        $xmlWriter.WriteEndElement()
        $xmlWriter.WriteStartElement("Operator")
        $xmlWriter.WriteElementString("Name", "$($Job.OperatorToEmail)")
        $xmlWriter.WriteElementString("Email", "$(($SqlServer.JobServer.Operators[$job.OperatorToEmail]).EmailAddress)")
        $xmlWriter.WriteElementString("NetSend", "$(($SqlServer.JobServer.Operators[$job.OperatorToNetSend]).NetSendAddress)")
        $xmlWriter.WriteElementString("Page", "$(($SqlServer.JobServer.Operators[$job.OperatorToPage]).PagerAddress)")
        # Write Close Tag for Root Element
        $xmlWriter.WriteEndElement() # <-- Closing OperatorElement
        $xmlWriter.WriteStartElement("Notification")
        $xmlWriter.WriteElementString("SendEmail", "$($Job.EmailLevel)")
        $xmlWriter.WriteElementString("SendEventLog", "$($Job.EventLogLevel)")
        $xmlWriter.WriteElementString("SendPage", "$($Job.PageLevel)")
        $xmlWriter.WriteElementString("SendNetSend", "$($Job.NetSendLevel)")
        $xmlWriter.WriteEndElement() # <-- Closing NotificationElement
        $xmlWriter.WriteStartElement("Schedules")
        $serverJobSchedules = $job.JobSchedules
        ForEach ($serverJobSchedule in $serverJobSchedules) {
            $xmlWriter.WriteStartElement("Schedule")
            $scheduleNameRedacted = $serverJobSchedule.Name.replace(" ", "")
            $scheduleNameRedacted = $scheduleNameRedacted.replace('[^a-zA-Z]', "")
            $xmlWriter.WriteAttributeString("Include", "$scheduleNameRedacted")
            $xmlWriter.WriteElementString("Name", "$($serverJobSchedule.Name)")
            $xmlWriter.WriteElementString("Enabled", "$($serverJobSchedule.IsEnabled)")
            #$xmlWriter.WriteElementString("Enabled", "$($serverJobSchedule.IsEnabled -replace "$", '')")
            $xmlWriter.WriteStartElement("Frequency")
            $xmlWriter.WriteElementString("Type", "$($serverJobSchedule.FrequencyTypes)")
            if ($serverJobSchedule.Frequencytypes -eq "Weekly") {
                $range = 1, 2, 4, 8, 16, 32, 62, 64, 65, 127
                $combi = Get-SumUp -numbers $range -target $serverJobSchedule.FrequencyInterval
                if ($combi.part.count -gt 1) {
                    [string[]]$combo = $combi.part[-1].Split()
                }
                else {
                    [string[]]$combo = $combi.part.Split()
                }
                [int[]]$combee = [int[]]$combo
                $WeeklyFrequencyInterval = Get-DaysOfWeek -enum $combee
                foreach ($w in $WeeklyFrequencyInterval) {
                    $xmlWriter.WriteElementString("Interval", "$($w)")
                }
            }
            elseif ($serverJobSchedule.Frequencytypes -eq "Monthly" -or $serverJobSchedule.Frequencytypes -eq "Daily") {
                $xmlWriter.WriteElementString("Interval", "$($serverJobSchedule.FrequencyInterval)")
            }
            $xmlWriter.WriteElementString("Recurrs", "$($serverJobSchedule.FrequencyRecurrenceFactor)")
            $xmlWriter.WriteEndElement() # <-- Closing FrequencyElement
            $xmlWriter.WriteStartElement("DailyFrequency")
            $xmlWriter.WriteElementString("Every", "$($serverJobSchedule.FrequencySubDayTypes)")
            Write-Host $serverJobSchedule.FrequencySubDayTypes  -ForegroundColor DarkGreen
            Write-Host $serverJobSchedule.FrequencySubDayInterval -ForegroundColor DarkMagenta
            if ($serverJobSchedule.FrequencySubDayTypes -ne "Unknown") {
                $xmlWriter.WriteElementString("Interval", "$($serverJobSchedule.FrequencySubDayInterval)") 
            }
            $StartTimeSpan = $serverJobSchedule.ActiveStartTimeOfDay -split ":"
            $EndTimeSpan = $serverJobSchedule.ActiveEndTimeOfDay -split ":"
            $xmlWriter.WriteElementString("StartHour", "$($startTimeSpan[0])")
            $xmlWriter.WriteElementString("StartMinute", "$($startTimeSpan[1])")
            $xmlWriter.WriteElementString("StartSecond", "$($startTimeSpan[2])")
            if ($serverJobSchedule.FrequencyTypes -ne "OneTime") {
                $xmlWriter.WriteElementString("EndHour", "$($endTimeSpan[0])")
                $xmlWriter.WriteElementString("EndMinute", "$($endTimeSpan[1])")
                $xmlWriter.WriteElementString("EndSecond", "$($endTimeSpan[2])")
            }
            $xmlWriter.WriteEndElement() # <-- Closing ScheduleElement
            $xmlWriter.WriteElementString("StartDate", "$(Get-Date $serverJobSchedule.ActiveStartDate -format yyyy-MM-dd)")
            $xmlWriter.WriteElementString("EndDate", "$(Get-Date $serverJobSchedule.ActiveEndDate -format yyyy-MM-dd)")
            $xmlWriter.WriteEndElement() # <-- Closing ScheduleElement
            #Clear-Variable -Name "serverJobSchedule.Name"
            Clear-Variable -Name "serverJobSchedule"
        }
        $xmlWriter.WriteEndElement() # <-- Closing SchedulesElement
        $xmlWriter.WriteStartElement("Steps")
        $JobSteps = $job.JobSteps
        foreach ($Step in $JobSteps) {
            $xmlWriter.WriteStartElement("Step")
            $xmlWriter.WriteElementString("Name", "$($step.name)")
            $xmlWriter.WriteElementString("SubSystem", "$($step.subSystem)")
            $xmlWriter.WriteStartElement("RunAs")
            $xmlWriter.WriteAttributeString("Include", "RunAsAccount")
            $xmlWriter.WriteElementString("Name", "$($step.ProxyName)")
            $xmlWriter.WriteEndElement()#<- End RunAs
            if ($step.subSystem -eq "PowerShell" -or $step.subSystem -eq "TransactSql") {
                $xmlWriter.WriteElementString("Command", "$($step.Command)")
            }
            if ($step.subSystem -eq "Ssis") {
                $StepCommand = $Step.Command
                $pattern = '(?<=SERVER ).\w([^\s]+)'
                [String]$SsisServer = [regex]::match($StepCommand, $pattern)
                $xmlWriter.WriteStartElement("SsisServer")
                $xmlWriter.WriteAttributeString("Include", "IntegrationServicesCatalogServer")
                $xmlWriter.WriteElementString("Name", "$($ssisServer)")
                $xmlWriter.WriteEndElement() #<- Closing SsisServer
                $xmlWriter.WriteStartElement("SsisServerDetails")
                $stepNameRedacted = $step.name.replace(" ", "")
                $stepNameRedacted = $stepNameRedacted.replace('[^a-zA-Z]', "")
                $xmlWriter.WriteAttributeString("Include", "$($stepNameRedacted)")
                $pattern = 'SSISDB.*.dtsx'
                [String]$ssisCatalog = [regex]::match($StepCommand, $pattern)
                $SsisProperties = $ssisCatalog.Split('\')
                $xmlWriter.WriteElementString("SsisServerCatalog", "$($SsisProperties[0])")
                $xmlWriter.WriteElementString("SsisServerCatalogFolder", "$($SsisProperties[1])")
                $xmlWriter.WriteElementString("SsisServerCatalogProject", "$($SsisProperties[2])")
                $xmlWriter.WriteElementString("SsisServerCatalogPackage", "$($SsisProperties[3])")
                $pattern = '(?<=ENVREFERENCE).[0-9]*'
                [string]$EnvReference = [regex]::match($StepCommand, $pattern)
                if ($EnvReference -match "[0-9]") {
                    $script = "SELECT [environment_name]
                            FROM [SSISDB].[catalog].[environment_references] er
                            WHERE er.reference_id = $($EnvReference.Trim(' '))"

                    try {
                        $ssisEnvironment = $SqlServer.ConnectionContext.ExecuteScalar($script)
                    }
                    catch {
                        throw $_.Exception
                    }
                    $xmlWriter.WriteElementString("SsisServerCatalogEnvironment", "$($ssisEnvironment)")
                }
                $xmlWriter.WriteEndElement() # <-- Closing Step
            }
            $xmlWriter.WriteElementString("OnSuccessAction", "$($step.OnSuccessAction)")
            if ($step.OnSuccessAction -eq "GoToStep") {
                $OnSuccessGoToStepName = Get-GoToStepName -JobObject $job -StepId $step.OnSuccessStep
                $xmlWriter.WriteElementString("OnSuccessStep", "$($OnSuccessGoToStepName)")
            }
            $xmlWriter.WriteElementString("OnFailAction", "$($step.OnFailAction)")
            if ($step.OnFailAction -eq "GoToStep") {
                $OnFailGoToStepName = Get-GoToStepName -JobObject $job -StepId $step.OnFailStep
                $xmlWriter.WriteElementString("OnFailStep", "$($OnFailGoToStepName)")
            }
            $xmlWriter.WriteElementString("RetryAttempts", "$($step.RetryAttempts)")
            $xmlWriter.WriteElementString("RetryInterval", "$($step.RetryInterval)")
            $xmlWriter.WriteElementString("Databasename", "$($step.DatabaseName)")
            $xmlWriter.WriteEndElement() # <-- Closing Step
        }
        $xmlWriter.WriteEndElement() # <-- Closing Steps
        $xmlWriter.WriteEndElement() # <-- Closing JobElement
        $xmlWriter.WriteEndDocument()
        $xmlWriter.Finalize
        $xmlWriter.Flush | Out-Null
        $xmlWriter.Close()
    }
    Remove-Variable -Name "serverJobs"
}