question

If I am not mistaken, when a SQL Agent job calls a PowerShell task, that task is executed outside of SQL Server, similar to executing xp_cmdshell. If you pass a wrong parameter to the Posh script, it will still call PowerShell successfully. You may want to test the scripts first in the PowerShell ISE.

PowerShell test-path evaluates whether the file exists and returns a true or false condition, not an error. You have to manually force that error condition if the file does not exist. The following does that if (test-path \\server\drive\filename.txt) { write-output "File exists" } else { write-error "File does not exist" }

If you want a PoverShell script in the agent to end with failure a critical error needs to occur. This is not a case of `Wrire-Error` as it simply writes an error message, but the pipeline continues. If you really want to end with failure, you need to throw exception in the script. If you put the script below in the agent PowerShell script, in case the `$fileToTest` doesn't exists, the agent step will end with failure. If `Write-Error` was used instead of `throw` then the step will end with success. $fileToTest = "\\server\path\filetotest.xlsx" if (Test-Path $fileToTest) { Write-Output "File [$fileToTest] exists" } else { throw "File [$fileToTest] does not exists" }