I went to the edge of the Internet, and could not find the answer to my Powershell question. Say I returned exit code from Powershell. SQL Agent, running the command from CmdExec, would always receive an exit code of 0. It didn’t matter what I tried from all of the suggestions. The Powershell would fail, but Agent always thought that it succeeded.
This solution ended up working. Use “[Environment]::Exit(1);” rather than “Exit 1;” In SQL Agent, you can look for a box in the job step area for CmdShell, and tell it what exit code represents success. You’d want to change this to 1, if that represents success for your script. The StackOverflow question can be found here: http://stackoverflow.com/questions/8902004/powershell-fails-to-return-proper-exit-code
Although the question shows a solution, it doesn’t state any reasons. I found that it was because of [Parameter(Mandatory=$True)] in the parameter portion of the Powershell script. My script had this, and so does the one in the StackOverflow question.
Once I ripped out [Parameter(Mandatory=$True)] from the script, it processed the exit codes normally. Now, this is a little inconvenient, since I do want those parameters to be mandatory. With this knowledge, I was able to find the StackOverflow question that pointed to the answer. Use [Environment]::Exit(1);