PowerShell exit codes, or "Why does SQL Server think my PowerShell script succeeded?"

If you search for PowerShell logging, or PowerShell output, you'll find lots of folks who are frustrated by PowerShell's error handling and logging capabilities.
It turns out, they're pretty robust, but because PowerShell has separate error and standard output paths, we don't always see the errors in the way we'd expect.  This is especially true with automation.

Here's a quick example, just to demonstrate one problem that caught us once.

Our SQL Server instances run under accounts that have never logged in to the server interactively.  We had a PowerShell script that was processing some web service APIs, and it wasn't returning any information.  Here's a simplified script:
C:\windows\system32\WindowsPowerShell\v1.0\powershell.exe -command "invoke-webrequest https://www.microsoft.com"

If we ran this manually (or, say, in a debug SSIS window), all works well.  No errors.

If we ran this as a SQL Agent job, also no errors.  But no output. 

Trap the Errors

There's a simple addition we can make to trap our errors and control the exit code:  trap.  Just put this at the beginning of your script:
trap { write-output $_ exit 1 }

When we run our script with this addition, we get something altogether different:
The response content cannot be parsed because the Internet   Explorer engine is not available, or Internet Explorer's first-launch   configuration is not complete. Specify the UseBasicParsing parameter and try again.

Error handling, even in OS scripts, is really important.