How can you tell if a file has finished downloadin

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How can you tell if a file has finished downloadin

Author

Topic

PutJatDa
Starting Member

12 Posts

Posted - 2006-05-03 : 06:57:09

Currently we have a process where 4 files are ftp'd down to our server. We have DTS package which tests for the existence of these files and once these exist, the DTS package loads the data from these files into a SQL database. Sometimes, however, though the files exist they haven't finished being downloaded so that when the DTS package tries to process them the package is saying the files are empty (though they're not).

Thanks for any help.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 07:02:51

Usually the files are locked until they are finished but ftp sometimes has a problem with releasing the lock before the last block has finished.
I suspect you are getting an error that the file is locked rather than an empty file.

Best is to get the download process to rename the file when it is complete.
Otherwise put in a rename process before your import - that should fail if the file is still being downloaded.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PutJatDa
Starting Member

12 Posts

Posted - 2006-05-03 : 07:21:50

Thanks nr and thanks for the explanation.

PutJatDa
Starting Member

12 Posts

Posted - 2006-05-03 : 07:49:52

I agree that the best is to get the download process to rename the file when it is complete.

However, as a matter of interest, as I don't know, what is the code for renaming? Can you point me in some direction?
The code to test for the existence of files is in an ActiveX task in the DTS package :-

Dim FS, FL

Set FS = CreateObject("Scripting.FileSystemObject")

IF FS.FileExists(DTSGlobalVariables("AccountSrcFileName").Value) then
Set FL = FS.GetFile(DTSGlobalVariables("AccountSrcFileName").Value)
MAIN =DTSTaskExecResult_Success

ELSE
MAIN =DTSTaskExecResult_Failure

END IF

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-03 : 08:42:20

FS.MoveFile

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PutJatDa
Starting Member

12 Posts

Posted - 2006-07-14 : 12:04:08

I'm trying to rename a file in an Activex Script within a DTS package.
However, the renaming isn't working. The code is as follows. Any help is appreciated.

Function MAIN()

Dim FS, FL

Set FS = CreateObject("Scripting.FileSystemObject")

On Error Resume Next

Set FL = FS.OpenTextFile("\\<servername>\ExitControlFile.txt")

If Err.Number <> 0 then
MAIN =DTSTaskExecResult_Failure
Else
MAIN =DTSTaskExecResult_Success
FL.MoveFile ("\\<servername>\ExitControlFile_Processed.txt")
FL.Close
end if

IF MAIN =DTSTaskExecResult_Failure then

----------------

END IF

End Function

I've also tried
FL.MoveFile ("\\<servername>\ExitControlFile.txt"),
("\\<servername>\ExitControlFile_Processed.txt")

and without the brackets surrounding the file names.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-14 : 19:47:15

movefile spource, dest

and you probably can't move while it's open.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources