How can I catch a .ps1 error when its called from CmdExec?

Question

I have a SQL Agent job that dynamically builds a .ps1 to copy files, then uses a CmdExec job step to check it exists and run it. If there's nothing to copy the .ps1 won't get built. But if I have an invalid path or other .ps1 error, the CmdExec job step still reports success. I don't know how to get the CmdExec to catch an error in the .ps1 .

if exists c:\folder\BatchCopy.ps1 Powershell -file "c:\folder\BatchCopy.ps1"

I believe the key is to get your PS script to return a non-zero %ErrorLevel%. Look [here](https://stackoverflow.com/questions/12263337/), [here](https://stackoverflow.com/questions/9111752/) and [10 Tips for the SQL Server PowerShell Scripter](https://devblogs.microsoft.com/scripting/10-tips-for-the-sql-server-powershell-scripter/). Please post back what you find. — paulsm4, Nov 01 '19 at 18:19

score 0 · Answer 1 · answered Nov 01 '19 at 19:46

I figured this out by simplifying it. Instead of using CmdExec to run Powershell, just use Powershell. If the job step fails, the job will fail and I'll know about it.

    # If the batch script was built - run it
    c:
    $fileToCheck = "c:\folder\BatchCopy.ps1"
    if (Test-Path $fileToCheck -PathType leaf)
    {
        & "c:\folder\BatchCopy.ps1"
    }

How can I catch a .ps1 error when its called from CmdExec?

1 Answers1