August 24, 2001 at 8:20 am
What happens when a DTS is scheduled to run every 30 minutes to look for a file using the FileExists method and the file is still in the process of having data inserted into it from another process? Will DTS pick up the partial file or will it wait until the file is finished loading?
Rob DeMotsis
Sr. SQL Server DBA
August 24, 2001 at 9:27 pm
I would guess that it depends on what is doing the writing, whether or not it allows shared access to the file. Might be fun to test.
In general I'd say program defensively, assume it could happen. Maybe alter the file creation process so that it does an insert into a "queue" table when it's done, let the DTS package process the rows in the table to see which files are available. A less elegant solution would be to look for an "end of file" pattern that would you know it was complete.
Brian - experience with this?
Andy
August 25, 2001 at 9:33 am
When we bring over large extract files from our mainframe systems, what we do is put a small (100 bytes or so) "tickler" file to tell us that the other files have come over and are fully copied. You could use the same sort of technique here... The job that's inserting could create a tickler file and you look for it. If you don't see it, reschedule the job for X minutes later. If you do see it, delete the file and kick off the DTS package.
K. Brian Kelley
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply