"File Watcher" for SQL Server - Page 3

Create Schedule

Create the Job schedule to run from Monday to Friday from 6AM to 9AM.

How does it work?

When the job "File Watcher" starts at 6am, it will look for the C:\FileImport\File.csv. If the file does not exist, it will insert 'Skipped DTS - File not found' into the "logtable."

This job runs every 5 minutes. When the file is copied from the external source to this folder and once the job runs, the next time it will execute the job "test" that in turn runs the DTS package to import the file. The DTS package also renames the file with the month and day as suffix to the file.

When the job "File Watcher" runs, after the file has been imported, it inserts 'Skipped DTS - Already Imported' into the "logtable" every five minutes.

When you query the log table, it produces results similar to those below.

Issues

Sometimes, when the File.csv is being copied and if the DTS job is running at that time, the DTS fails because it cannot get Exclusive access to the File.csv. This issue can be solved in two different ways.

One way to resolve this issue is to wait for five more minutes; the "FileWatcher" job will catch up since the "FileWatcher" job runs every 5 minutes.

A second way of resolving this issue is to request the external source to send an indicator file, "indicator.txt," after the file has been copied. If we do that, we have to make some minor changes. Change the SQL code in the job with the statement below:

SQL Code

declare @Filestatus smallint
declare @rowstatus smallint
create table #tmp (FileExists smallint,FileisaDirectory smallint,ParentDirectoryExists smallint)
insert #tmp exec master.dbo.xp_fileexist 'c:\FileImport\indicator.txt'
set @Filestatus = (select FileExists from #tmp)
set @rowstatus = (select count(*) 
  from [Logtable] where convert(varchar(12),date, 112) =
  convert(varchar(12),getdate(), 112)  and status ='imported')
print @rowstatus 
print @Filestatus 
drop table #tmp
if @rowstatus <> 0 
begin
	insert into logtable(status) values ('Skipped DTS - Already Imported')
end
else
begin
	if @filestatus = 0
	begin 
		insert into logtable(status) values ('Skipped DTS - File not found')
	end
	else
	begin
		exec msdb.dbo.sp_start_job 'test'
	end
end

Change the activex statement in the DTS package with the one below

ActiveX code

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Function Main()
Set oFSO = CreateObject("Scripting.FileSystemObject")
sFilename = "c:\FileImport\FIle.csv"
oFilename ="C:\FileImport\FIle"+cstr(day(now()))+"-"+cstr(month(now()))+".csv"
ofso.MoveFile sFilename , oFilename
sFilename = "c:\FileImport\indicator.txt"
ofso.deleteFile sFilename 
Main = DTSTaskExecResult_Success
End Function

Note: Change the Filename, path information, job names according to your environment.

Conclusion

As mentioned before the main intent of this article is to watch for a file. When the file arrives, trigger a job to import that file to a table and rename the file with the current month and day as suffix.

» See All Articles by Columnist MAK