Process Has Flat File Locked

Question

Process Has Flat File Locked

cdun2

SSCertifiable

Points: 7893
More actions
October 5, 2007 at 1:02 pm

#73765

Hello,
I have a SQL Server 2000 DTS package that contains an ActiveX script to pull down one flat file per day from an FTP site. The script runs a shell command. The command fires and FTP script, and the file succesfully downloads. The portion of the script that where the shell command is run is as follows:
Set objShell = CreateObject( "WScript.Shell" )
objShell.Run ("ftp -i -n -s:" & chr(34) & strFTPScriptFileName & chr(34))
Set objShell = Nothing
Once the file is downloaded, the next step in the package uses a flat file source with a data pump that copies data from the flat file to a 'staging' table in SQL Server. The package fails at the point where the flat file source has to open the downloaded file:
"DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: The process cannot access the file because it is being used by another process. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"
The FTP script uses 'bye' in the last line. Is the shell holding a lock on the flat file, or could there be some other problem?
Thank you for your help!
CSDunn

Viewing 3 posts - 1 through 2 (of 2 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

I'm no expert in this area, but I don't think that just setting the shell to nothing will do it... don't you also have to drop or close the shell object to release the file?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Loner SSC-Insane Points: 21279 More actions · Answer 2

Instead of using Active X script, have you tried to use the FTP task in DTS?

I used it before and it seemed to work pretty good.