April 4, 2011 at 6:39 am
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 can'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.
April 4, 2011 at 1:56 pm
loki70 (4/4/2011)
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 can'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.
Unfortunately there isn't really a "better" way...that I know of at least. In my opinion, your only options would be a custom script task (as you have done before), or some alternative "On Error" workflow that loops if an error occurs. I personally would prefer the script task, because I think it is a bit cleaner...but other people might have different opinions.
April 5, 2011 at 12:47 am
I basically had the same problem, but after my big main file was written out, i would write out a small little 1bit file as well. I then check in SSIS to see if the small text file is there, and if so, I know that my source system has already written out the main file.
If your source system can write out a small file after the main file, it would sort it out.
April 5, 2011 at 8:12 pm
Thanks for the feedback, guys.
Unfortunately, I have 0% control of the process that is generating these files (long story). So unfortunately, I could not use your idea to write a tiny file after the main one is written. Good idea though!
I will probably go back to write that custom script. At least it will be somewhat easier to write in .NET.
If anyone has any other suggestions, please chime in
Thank you again for your replies!
April 7, 2011 at 7:35 am
Check out the File Watcher task from Konesans (http://www.konesans.com/products/file-watcher-task.aspx).
According to their web-site : "The task will detect changes to existing files as well as new files; both actions will cause the file to be found when available. A file is available when the task can open it exclusively. This is important for files that take a long time to be written, such as large files, or those that are just written slowly or delivered via a slow network link."
April 7, 2011 at 1:34 pm
Grasshopper,
Great idea; I had not heard of that component. I will experiment with it and post back here with my results.
Thank you!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply