Returning Failure if File does not exist

Question

I am running the below script in SQL Server Agent.

All the subsequent steps in job are dependent upon this step. This is pretty simple Looking for a file and exits with failure if file is not found.

  $file = "C:\\Data\\FileDrop\\.done"
  $CheckFile = Test-Path -Path $file

  if (!($CheckFile))  {exit 1 } 

However when the agent job runs, it says the step failed because file not found and existing with code 0 - success.

What am I doing wrong here?

Answer 1

I don't think the return value / error code of the job has anything to do with whatever value the script returns.

If you want to fail with an error message, try this:

# make sure to stop on errors
$ErrorActionPreference = 'Stop'
$path = 'C:\Data\FileDrop\.done'
# check for file explicitly (in case a directory with that name exists)
if(![System.IO.File]::Exists($path)) {
    # throwing an exception will abort the job
    throw (New-Object System.IO.FileNotFoundException("File not found: $path", $path))
}
# anything after this will not be executed on error...

This should fail the job entirely and show the error message in the job's history.