Is Text File Complete and Available

[Tinkerers]

Hi,

I have an FTP site where a remote host puts giant text files every so often. I need to programetically determine if the file exists and if the remote copy process has completed so I can then run some code to retrieve the file. Retrieiving the file is no problem and determining if the file exists is no problem.

My problem lies with determining if the copy process has completed. It seems Windows allocates the full file size in the FTP site, and as the file is copying the size doesn't increase, as it has already allocated the full file size. But in reality the file has not completed the copy process yet.

How can I determine if the file has completed and is ready to be bulk inserted into sql server?

As a separate issue, it sure would be nice to have some routine kick off a job in sql server every time a new file shows up in the ftp site, though I have no idea how to implement this. Seems I would need some type of daemon running in windows to monitor files in the ftp site and then call a job through a command line.

Thanks for any help,
Paul

Thanks,
Paul

 

[SimonEireconsulting]

I have done something similar in the past where by we:

1. transferred the large data file
2. created a file with the same name in another directory AFTER the large data file transfer was completed.
3. looked in the other directory to see if any files had been transferred and process from there.

To answer your second issue, you can write a VB script file that will look in the directory and process a file if needed. This processing can include calling a stored procedure to do the bulk insert (how are you doing this at the moment?). Then you just need to schedule this vb script file on the server (Scheduled tasks in Admin Tools)

 

[philhege]

Simon, how did you determine that the transfer in step 2 was complete? I believe that's the crux of Paul's problem.

You could inspect the FTP log for the transfer complete message, I suppose.

Take a look at this thread for a good discussion of what happens during file copy operations.

Good luck!

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[SimonEireconsulting]

There was no need to see if the transfer in step 2 was complete. If the file exists in step 2 then the file transfer in step 1 was complete. That is all that is needed to know.

Come to think about it, we may have been doing ftp get instead of relying on others to do an ftp put.... Can't remember - it was 10 years ago.....

 

[SQLSister]

We usually have a flag file created after the first file is copied. (our ftp app does this, so it isn't handled within SQL) Or we have the provider send a flag file which has the number of records in the main file in it as the content. Then I import both files to a work table and check the count of the real table against the number in the flag file. This is especially critical for us as we have some extremely large files sent and the customer wants a verifiable process that we did in fact have the whole file when we processed.

"NOTHING is more important in a database than integrity." ESquared

 

[Tinkerers]

Guess there's not an easy way to determine if the file is complete. Some of these files are over 1 GB in length and take a long time to transfer. It was a hassle just convincing the sender to place the files in our FTP site. I don't think I'll be able to talk them into setting up anything additional, uggghhhh.