Pfthb

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?

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:DataFileDrop.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.

Maybe this is what you're looking for:

if(!(Test-Path "C:DataFileDrop.done"))
 return 1

else 
 return 0

This is not going to be a great answer because I have not found the documentation that explains this but the use of Exit followed by a number seems to not have any effect on the exit code.

The following example works, with the general idea taken from a blog.
First create a test script like the following:

$host.SetShouldExit(12345)
exit

Next call this script from the Powershell command line as in the following example:

$command = "C:YourTestScriptPathAndName.ps1"
Powershell -NonInteractive -NoProfile -Command $command

The script will run and exit, and we can then check the exit code from the Powershell prompt by writing out the contents of $LASTEXISTCODE

$LASTEXITCODE

In this case $LASTEXISTCODE would contain 12345.

In the case of using this with Test-Path it would look like this:

#using a path that does not exist
if(Test-Path -Path 'C:nope')
 $host.SetShouldExit(1234)

else
 $host.SetShouldExit(2222)

This sets the exit code to 2222 when invoked from the command line, but when I invoke that test script from another script instead of from the command line the $LASTEXITCODE variable is set to 0 still.