I am running SQL Server 2005. I have a job that has six steps to load a database table from an external source. The external source is required to send the file nightly. In system testing I found this is not always the case. I created a batch 'step' (Step 3) to check if the file has been sent (see code below). If the file is present I use today’s file. If the file is not present I use last night's file (demographic data). I cannot set Step 3 ‘Job Step Properties – Advance tab’ to ‘on failure action’ because logically it will not fail. I want to modify the code to ‘move’ last nights’ file and then indicate the step has failed (force failure – set the job step status property?). Then I would execute the next step (Step 4) that notifies the external source of not receiving the file. I only want to execute Step 4 when I do not receive the file. How do I set the job step status property in Step 3 to indicate 'failure' after moving the file? Does anyone have any solutions? STEP 3 code @echo off Retailer file transfer. if exist "E:SQL DatabasesSQL JobsSQL Import DataGPRetailer.del" goto done echo NO RETAILER FILE TO TRANSFER! :begin move "E:SQL DatabasesSQL JobsSQL Import DataGPRetailer.old" "E:SQL DatabasesSQL JobsSQL Import DataGPRetailer.del" echo Moved! :done echo Done!

Software/Hardware used:
Microsoft SQL Server 2005 SP3