Posted by Roy Harvey on 12/07/06 04:10

The approach we used was that along with every set of files that were
FTPed, one extra file was transfered after all the rest were done. The
presence of that flag file indicated that the set of data was
complete; THAT was the file the load process waited for. Once the BCP
steps for the data files were done, the flag file was deleted, and the
wait for the next flag file began over again.

Roy Harvey
Beacon Falls, CT

On Wed, 6 Dec 2006 14:55:18 -0800, "Tim Satterwhite"
<timothy.satterwhite@nospamucsfmedctr.org> wrote:

>Hi All,
>
>I think this is a thorny problem, and I'm hoping you can help. I've not
>found this exact issue described anywhere yet.
>
>I have a stored procedure that calls BULK INSERT on a set of text files.
>These files are FTP'd from a legacy system (a mainframe running MVS).
>Sometimes, the process steps on iteslf, whereby the bulk insert is attempted
>on a file whose FTP is still in progress; it's not fully written to disk on
>the SQL box (it's a 100MB file that takes a long time to cross the network
>to the share on the Windows box), and so the insert generates a fatal error
>and the procedure stops.
>
>I wrote a proc that calls the sp_OA* procs to use the
>Scripting.FileSystemObject to test for the file's readiness. It returns an
>error for me if the file does not exist. It also returns an error if I try
>to run the BULK INSERT against a file which is being copied via Windows to
>the SQL box. This is working just fine under those conditions; the
>sp_OAMethod to call OpenTextFile bombs appropriately if the file write is
>still in progress.
>
>That's great, but it doesn't do the same thing during an FTP in progress.
>It doesn't generate the same error (that is OpenTextFile has no problem
>opening a partially written FTP'd file). I can also open the file in
>Notepad, even though it's not fully written to disk ... something I did not
>expect, but there we are. What is it about FTP that's different from a
>Windows file system copy operation that makes the file look available for
>reading?
>
>If BULK INSERT is capable of detecting that it cannot do its thing on a file
>whose FTP is in progress, then what can I write or call to emulate that
>detection? I tried writing a COM object in VB.NET and calling that from my
>SQL stored proc instead of the Scripting Engine's FSO methods. My code
>simlpy tries to run a FileOpen using an Exclusive read lock; however, this
>doesn't seem to work, and I'm shooting in the dark now.
>
>Can anyone tell me what kind of file i/o request BULK INSERT makes, such
>that it is capable of abending during a run against an incompletely written
>file using FTP?
>
>Thanks!
>Tim
>

[Back to original message]