Automatically Import Text File When Available

[DanEvans]

Hi folks,
A challenging problem (for me!).
I need to import any one of 25 large fixed width text files when the following two conditions are met:

1. The file exists (has been downloaded) and
2. The download process has finished writing to the file
(file not in use).

Basically, when the file is available for import, I want a process to be triggered, or I want to trigger a process that checks for availability periodically.

I use BCP to import these files when they are available, since I don't know a way to automate this process. I have tried the following:

1. I have tested the file existence using a VbScript filesystmeobject..if I could test for file in use, and make the step wait five minutes if the file is in use, this would be the perfect solution because I could put it in DTS (but I don't know how).

2. I have use xp_fileexist, but it doesn't help with the file in use problem.

This is a plaguing problem, so any help you can provide would be appreciated! Thanks,
Dan

 

[clapag22]

If you know you only need to wait at most 5 minutes (like you said in step 1), why not use xp_fileexist and then issue a WAITFOR command. See BOL for use of WAITFOR.

 

[tlbroadbent]

Check the resources available at SQLDTS.com. Here is a link that will show you how to check if a file exists from a DTS package.

How can I check if a file exists?

http://www.sqldts.com/main.asp?nav=1,6,211,0

The next link is for an article that explains how to loop and process multiple files in a DTS package.

http://www.sqldts.com/main.asp?nav=1,6,246,0

Here are a couple of resources on SWYNK.com

http://www.swynk.com/friends/green/dtsdirlooppkg.asp

http://www.swynk.com/friends/green/dtsdirloopstep.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[wsmall73]

we have used a 'DONE' will which will tell you that the previous file has completed it's download in our case FTP is complete. Once we see that the file is downloaded we make a backup copy of it in archive renaming it with a date, BCP the file, and then delete both. Leaving a clean directory for the next day's process. There obviously many ways to accomplish this task. Here anothe for thought.

 

[DanEvans]

This has been very helpful!
wsmall73--I am not sure what you are referring to when you say you use a 'DONE'...could you explain a little about what you mean?

One idea is that we could schedule a job to start sometime in the morning that will try and import the file in question. I could set this job wait for a certain amount of time upon failure, then retry (for a given number of tries).

Another idea is that I could try to trap the specific error
(if @@Error = 1234) but the bulk insert failure prevent further code from running...do you know a way around this? Thanks,
Dan

 

[wsmall73]

Sorry Dan.. I have been out of the office. The done file is nothing more than a 'dummy file' that is ftp'ed directly behind the file in question. Since it will do these ftp's sequentially I know that once the 'done' file appears that the first file is complete and that the process has released it. Your dummy file can be nothing more than a file with one character in it. (Need at least a character for it to exist).

Once this process is complete you can simply do your bcp as designed.