sql-serverpowershellsql-server-2016sql-server-agent

How to run SQL Server Agent Powershell script with output and exit code


I'm trying to run a PowerShell script as a SQL Server 2016 Agent job.

As my Powershell script runs, I'm generating several lines of output using "Write-Output". I'd like to save this output to the job history, and I only want the job to continue to the next step if the step running the PowerShell script completes with an exit code of 0.

I'm using the "PowerShell" step type in my agent job. The Command text looks like this..

# Does some stuff that eventually sets the $resultState variable...

Write-Output ("Job complete with result '" + $resultState + "'")

if($resultState -eq "SUCCESS") {
    [Environment]::Exit(0);
}
else {
    [Environment]::Exit(1);
}

Under the "Advanced" settings, "Include step output in history" is checked. If I remove the final "if" statement from the PowerShell script, then I can see the output in the history, but the job step is always successful and moves on to the next step. If I include the if/else statements, the job step fails if $resultState does not equal "SUCCESS" (which is what I want), but I don't see my output anymore in the history for the job step.

Any suggestions?


Solution

  • I worked around this by saving all of my output lines to a single variable, and using Write-Error with -ErrorAction Stop if my result wasn't what I wanted. This isn't quite what I was trying to do at first, because this doesn't use the exit codes, but SQL Agent will correctly detect if the job step succeeded or not, and my output can still show up in the job history because it will be included in the error message.

    Updated code:

    # Does some stuff that sets the $resultState and saves output lines to $output...
    
    $output += "`r`nJob complete with result '$resultState'"
    
    if($resultState -eq "SUCCESS") {
        Write-Output ($output)
    }
    else {
        Write-Error ("`r`n" + $output) -ErrorAction Stop
    }