1

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
Holmes IV
  • 1,673
  • 2
  • 23
  • 47
  • How are you generating the files? What process are yo using? Depending on that you can build in a bool check that is only set if a file is generated, then only execute this step of the code if the bool is set to true. Are you using SSIS package? Other processes before it gets to this step? – Brad May 16 '18 at 18:47
  • Currently I am doing the same thing, that is, a CMDEXEC that calls a C# program that checked if files need to be generated and acts accordingly. – Holmes IV May 16 '18 at 18:48
  • Possible duplicate of [Check for file exists or not in sql server?](https://stackoverflow.com/questions/11740000/check-for-file-exists-or-not-in-sql-server) – Tab Alleman May 16 '18 at 19:34

1 Answers1

1

Try to check if it exists before moving.

IF EXIST \\fs2\it\OutPut\*.xml MOVE \\fs2\it\OutPut\*.xml \\fs2\it\outbox
SQLChao
  • 7,709
  • 1
  • 17
  • 32