March 14, 2002 at 4:09 pm
Is it possible in SQL Server to determine if a particular file has arrived at a certain location on the server/ network directory and soon as that file arrives to kick off a JOB? The job itself will import the data into a SQL Server table.
abdul
abdul
March 14, 2002 at 5:13 pm
Using SQL you'd have to poll the folder. Easy enough to run a job once a min to check, tricky part is that the file may not be complete when the job runs and starts your process. A good way to avoid this is to either build in some delay once the job realizes the file is present or have the process that puts the file there put a second "flag" file in the same folder, that way when you see it you know the real file is complete. If you need a quicker response than 1 min, or just want to do it a different way you could poll from a small VB app at any interval you want...even millisecond! There is also an api that will monitor a folder for changes and raise an event when it occurs - .Net supports this directly, have to work some in VB I think.
Andy
March 16, 2002 at 12:32 pm
We do this very task, and we've got some files that come across the mainframe that are quite large. What we poll for isn't the large file but a "tickler" file that is sent after the file to be imported. The large file may be 300-400 MB or so, and we don't want to catch it in the middle of transfer.
The way our job works is we look for the tickler file. If we see it, we begin processing. Once done processing, the job reschedules itself for the next day (working on a Tues-Sat run schedule). If the tickler file isn't there, it simply reschedules itself to start 15 minutes later... unless it is 8AM. In that case the job sends an email saying the file wasn't available and reschedules for the next business day.
The process has worked pretty well for us. There are also some tools out there such as Argent which can poll for a file, and upon its arrival, it kicks off a batch file. It's a simple matter of having the batch file use isql or osql to start the job via T-SQL. We'll be looking into that later in the year.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 3, 2002 at 4:18 pm
Thanks.
I have done it..
I have used the sql server stored proc. sp_update_jobscehdule and some TSQL to check for current time and have achieved what I wanted. Basically I look for a flag file called Fladddmmyy.txt (where ddmmyy is worked out for the current day minus 1). If the file does not arrive I just reschedule my job to run in fifteen minutes time using the sp_Update_schedule, this rescehduling process continue until a cut out time is reached or the file has arrived. In either case I reschedule the job to run the next day at 00:30 (Tuesday to Saturday). I also log my action in a sql server table called t_progress in all cases, a text log file also resides on the server logging the events of the job too.
Thanks for your help
abdul
abdul
April 3, 2002 at 5:34 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply