May 7, 2009 at 5:14 am
Hi,
I have a bit of a problem with SSIS that i've been trying to work out for hours now and I'm just not getting anywhere. This is the first SSIS package i've ever written so i'm hoping the answer will be obvious, though I'm also hoping it's not so obvious I kick myself for not seeing it beforehand 🙂
First, some background. We currently have a DTS package running on a 2000 instance that checks a folder for the presence of one or more excel spreadsheets. Based on what type of spreadsheet it finds, one of two other packages is then run that reads the file into a holding table in SQL Server 2000. It then moves the file to an archive folder and changes the file name to match the record ID retrieved when inserting the information about the import taking place into a history table.
The instance on which this runs is now due to be retired and is being replaced by a 2005 instance, which means the DTS package needs to be rewritten as well. Because of the reliance on scripts in the 2000 DTS package and a desire to learn the SSIS ropes, I felt it would be better to rewrite the package within SSIS and while it has taken a while, I have almost finished. However I do have a problem I just can't seem to get around
The structure I have selected changes things from the old way of doing it somewhat. Since SSIS has the foreach loop, I simply use an expression to determine which route each file should take and process them in the one package. My problem comes when I try to move the file to the archive folder. I get the following error message
[font="Courier New"]Error: 0xC002F304 at Rename File LTR, File System Task: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.".[/font]
My interpretation of this message is that SSIS still has the file locked for reading the data out, and so I can't move the file until it has been unlocked. I know these files aren't going to be locked by anything else because I add them to the directory moments before the import takes place, and no file activity at all takes place on that file before the SSIS routine is run. However the data flow responsible for the data export has finished at this stage, why would the file still be locked? So far I've tried
- Introducing a delay between the two processes. It was suggested online in a similar problem that by waiting a pre-defined length of time, the file may unlock and allow me to perform the task. This did not work.
- Moving the file rename process. At first, the file rename was the next step after the dataflow and to separate them i tried removing the file duties from the first foreach loop and introducing a second loop after the first to just move the files to the other location, without renaming them for now. This didn't work either and I got the same error.
I have tried using Process Monitor to watch what is happening but this confuses me even more because immediately before what I guess to be the problem, PM reports the following
Date & Time:07/05/2009 12:04:43
Event Class:File System
Operation:CloseFile
Result:SUCCESS
Path:C:\RBSImport\Import\ST140608.XLS
TID:4816
Duration:0.0000081
and then I get
Date & Time:07/05/2009 12:04:43
Event Class:File System
Operation:CreateFile
Result:SHARING VIOLATION
Path:C:\RBSImport\Import\ST140608.XLS
TID:4816
Duration:0.0000164
Desired Access:Read Attributes, Delete, Synchronize
Disposition:Open
Options:Synchronous IO Non-Alert, Open Reparse Point
Attributes:n/a
ShareMode:Read, Write, Delete
AllocationSize:n/a
CreateFile is apparently used for both creating and opening, so while it seemed at first SSIS was trying to create a file of the same name as the existing one in the same location, I do not believe that to be the case.
I really am stumped now. The fact there is next to nothing on the net about this suggests it isn't expected behaviour and to be honest, if it was I'd be worried about the future of SSIS as it currently is, because wanting to move a file after reading out of it is a pretty straightforward task, so what am I doing wrong?
Thanks
Paul
May 8, 2009 at 3:02 am
OK, i figured out my error, and i thought i'd share it should anyone else have the same problem. What I had done at some point, while troubleshooting, was set my excel connection to have RetainSameConnection=true. There was nothing in the way of a description of this functionality within SSIS so I was unaware of the implications of this. By setting it to false I released the file after reading from it and my package completed successfully.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply