Failing a PowerShell Sql Agent step

SQL Server Agent offers some great branching facilities to cope with step failures. These work really well with T-SQL and SSIS steps, but how can you get your PowerShell steps to respond properly to cause a fail?

This question was put to me at SQL Bits over the weekend. The asker was using PowerShell to move files around prior to importing them. They wanted the PowerShell step to report failure properly so their Agent logic would rerun the step

You might want the same response to make sure your monitoring is letting you know when jobs fail. On that note, it would also be nice if you could raise an error or failure message in your PowerShell step and have that propagate back up to SQL Server

Unfortunately the usual scripting standbys of returning 0 or $false don’t work.

The solution is to Throw an error. This gives a simple way to register a failed step and raise an intelligible error back to SQL Server. At it’s most basic it’s just adding this line to your PowerShell:

For example:

Throw "File Not Found"

So in the example I was looking at you might have some like this:

If (-not (Test-Path -Path c:\imports\file.csv)){
    Throw "File c:\imports\file.csv does not exist"
} 
# And the rest of the script goes here

So now if the file is missing, the job step will fail AND you’ll have a nice error message in the job history.

For even move information you can have multiple throws in your script:

If (-not (Test-Path -Path c:\imports\file.csv)){
    Throw "File c:\imports\file.csv does not exist"
} 
$content = Get-Content c:\imports\file.csv -raw
if ($content.length -eq 0){
    Throw "File is empty"
}

So you can really narrow down the reasons for the SQL Agent job step failing.

This technique works with both the PowerShell and CmdExec SQL Agent step methods of running PowerShell scripts.