I have a similar situation, except the files we receive (approx 30 files) are sent more frequently. The source system is scheduled to send the files every :15 throughout the day at :00, :15, :30 and :45. The problem is, depending on the file size and load on the source server, the files can arrive anywhere in that 15 minute window.

The expectation is that we try to stay as current as possible on this data, so we're checking for and processing updates every 5 minutes. Unfortunately, about 30% of the time, we end up grabbing a file as it's being written and end up with a partial file. We copy it from our FTP/File server to our SQL server for processing. I wish the senders could do a rename on the file after it's been written, but that's not currently an option. Since FTP doesn't lock the file, the Konesan's File Watcher task doesn't do us much good as it's able to open the file at any point as the file is being written.

I'm contemplating a solution that checks the file sizes after we've robocopied the files to the SQL server to see if the file sizes match. If they do, great, if not, re-run the robocopy script and keep checking until it does.

Curious what others might do in this situation?