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

Question

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?

Answer 1

I struggled with this. The logged output from the Sql Job Powershell steps is pretty useless. I found it better to use a CmdExex step instead that calls Powershell.exe.

In a Sql 2019 CmdExec job step you can just call your powershell script like this:

Powershell "F:\Temp\TestScript.ps1"

... and you'll get all the output (and you can log it to a file if you like). And if there's an error, the job stops properly.

In some earlier versions of SQL Server, Powershell errors would get logged but the job would continue to run (see here https://stackoverflow.com/a/53732722/22194 ), so you need to wrap your script in a try/catch to bubble up the error in a way SQL can deal with it:

Powershell.exe -command "try { & 'F:\Temp\TestScript.ps1'} catch { throw $_ }"

Note that if your script path has spaces in it you might get different problems, see here https://stackoverflow.com/a/45762288/22194