SQL Sever Agent Job Stop Failure if no file

Question

I have a job in SQL Server 2014 that runs an executable which may or may not generate files. Afterwards the next step moves all the files generated to an SFTP site. Everything works if files exist. The problem is this task run every 10 minutes and 95% of the time no files are generated. *the reason it runs this often is we have a 20 min window to get the files to the SFTP after the triggering mechanism that allows the exe to generate them.

My question is how can I setup the task to only report a failure if files exist and fail to move? If no files exist I do not care. I know I can do this in SSIS, but currently I have a CmdExec step as:

MOVE \\fs2\it\OutPut\*.xml \\fs2\it\outbox

Answer 1

Try to check if it exists before moving.

IF EXIST \\fs2\it\OutPut\*.xml MOVE \\fs2\it\OutPut\*.xml \\fs2\it\outbox