March 18, 2004 at 1:38 pm
I am looking for an easy way to check a directory to see if a trigger file has been ftp'ed. When we see the file, we want to kick off a DTS package.
This will allow us to extract our daily updates as soon as our OLTP has completed its nightly processing.
We do not want to run at a specified time via the Job Scheduler.
We have looked at a few shareware file monitoring programs but we would like to handle this inside SQL Server if possible.
March 22, 2004 at 8:00 am
This was removed by the editor as SPAM
March 23, 2004 at 3:28 am
This may or may not help, but I found this UDF on this site:
CREATE Function fn_FileAvailable (@FileName VarChar(255))
Returns Int
As
Begin
/*
Return Values
= 0 FileSystemObject can open the file, so deemed "available"
= -1 means FileSystemObject could not be created
= -2146828235 (0x800A0035) File Not Found
= -2146828218 (0x800A0046) Permission Denied (in use)
...etc
*/
DECLARE @fso int, @hr int, @file int, @Result Int
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT
IF @hr <> 0 BEGIN
EXEC sp_OAGetErrorInfo @fso -- Error Processing
Return -1
END
EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @file OUT, @FileName, 1
SELECT @Result = @hr
IF @hr = 0 BEGIN
EXEC @hr = sp_OAMethod @file, 'Close'
EXEC @hr = sp_OADestroy @file
END
EXEC @hr = sp_OADestroy @fso
Return @Result
End
This UDF checks for a file and will only return a zero when the file is available for further processing - it will return a non zero value if the file is still being FTP'd etc.
You can wrap this in a stored prcedure which loops round until the file is ready:
declare @dtEnd smalldatetime, @intRc int
set @dtEnd = dateadd(minute,1,getdate())
print @dtEnd
set @intRc = -1
while @intRc <> 0 and getdate() < @dtEnd begin
select @intRc = dbo.fn_FileAvailable ('name of file')
if @intRc <> 0 waitfor delay '00:00:05'
end
if @intRc <> 0 begin
set @strMessage = 'Time out waiting for file.'
return
end
NB: I put in an end time so that the loop will finish even if the file does not get created.
You could execute the SP from the Scheduler or within your DTS package which means that you can start the package early and it will wait for the OLTP file to appear. Your DTS package can then continue without any further delay.
HTH
Jeremy
March 23, 2004 at 10:34 am
Jeremy,
Thanks for the code. That works perfectly. Set it up to run as part of a job and as soon as the file comes in, the job gets a success code and kicks off the next step.
Thanks!
Ted
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply