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

Question

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

chinta purna chandra rao

SSC-Addicted

Points: 436
More actions
October 11, 2006 at 12:32 am

#174856

Hi,
I have a DTS package in production environment. This DTS package copies .txt file on application server to production server and writes to SQL Server. The problem here is when the file is copied from application server, the thread opened by SQL Server on application server is not closed when the file is copied. And it is causing the DTS package to fail next time saying that the file is use by another process and cannot access the file.
I am resolving this issue by manually closing the connection on application server. I am closing the connection at Computer Management-->open files
Both Application server and Production server are windows 2000 and Production server is having sql server 2000 with service pack3 (security patch, version 8.00.818)
Can anyone let me know why this is happening, is this issue related to SQL Server or windows? Kindly let me know asap, as this is an urgent issue. TIA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

lholman SSCrazy Points: 2810 More actions · Answer 1

Are you using an ActiveX object to copy the .txt file? If so are you cleaning up in your code, for example by setting the object to nothing e.g. 'Set objConn = Nothing' whilst also setting the return status of the DTS package on completion 'Main = DTSTaskExecResult_Success'?

Have you tried scheduling the DTS package instead of running it whilst open to see if that causes the same issue?

Please add some more detail about the DTS package.

ll

chinta purna chandra rao SSC-Addicted Points: 436 More actions · Answer 2

Hi

Thanks for your reply.

The first task in DTS package is a batch file. It delete existing .txt file and then copies all *.csv files to .txt file.

The second task is, the SQL Server opens a connection with .txt file and copies data to an existing table. The text file is on different server.

The issue is, the file opened is not closed. The package is completing successfully but not able to copy the data from nexttime. The option in workflowproperties of the connection like "close the connection on package completion" is also checked.

In job history it is writing like "The process cannot access the file because it is being used by another process".

Please let me know.

lholman SSCrazy Points: 2810 More actions · Answer 3

Have you tried copying the file to a share on the SQL Server in the step when you change the extension from *.csv to *.txt? This way the file will be local to the DTS package which is also more sensible just incase you have network interruptions when loading data.

ll

chinta purna chandra rao SSC-Addicted Points: 436 More actions · Answer 4

I have tried the same from development environment. It is working fine.

There is no network problem.

Before I check the option (close connection on package completion) the issue used to come twice or thrice in a week. After I have checked that option, the issue occured only once in 2 weeks.

If there is some network issue during the transfer of data, the copy will fail and it may give different error.

If I assumed anything wrong, Kindly let me know.