Handling file access locks while file is being built

I have a [SQL 2008] SSIS package that takes a CSV text file and moves it to a separate folder. Once it is in this folder, I import the data to SQL. The text file is being automatically generated by an outside program on a periodic schedule. The file is also pretty large, so it takes a while (~10 minutes) for it to be generated.

If I attempt to move this file (using a File System Task) WHILE the file is still being built, I get this error message:

"The process cannot access the file because it is being used by another process."

Which makes sense, since it c开发者_开发知识库an't move a file that is being accessed elsewhere. Back in DTS I wrote some custom script to check for a period of XX seconds to see if the file size had increased, but I was wondering how to handle this properly in SSIS. Surely there is a better way to determine if a file has locks on it before doing file operations.

I would greatly appreciate any suggestions or comments! Thank you.

Probably, you have found an answer to your question by now. This is for others who might stumble upon this question.

To achieve the functionality that you have described in your question, you can use the File Watcher Task that is available for free download from the website SQLIS.com.Click the link to visit File Watcher Task download page.

Hope that helps.