"File Watcher" for SQL Server

Wednesday Mar 3rd 2004 by Muthusamy Anantha Kumar aka The MAK
Share:

SQL Server Database administrators often come across situations where the scheduled DTS jobs fail because the Source File didn't arrive on time. Learn how to use 'File Watcher' to look for the existence of a file in a particular folder every few minutes and to trigger another job when the file arrives.

SQL Server Database administrators often come across situations where the scheduled DTS jobs failed because the Source File didn't arrive on time. It is a tedious process to cleanup if there are dependent jobs that have run before the first DTS job completed successfully. The intent of this article is to provide a solution for that kind of situation.

Let's simulate the whole scenario of "File Watcher" Job.

What is "File Watcher"

File Watcher is a SQL job that looks for the existence of a file in a particular folder every few minutes. If that particular file arrives, it will trigger another job.

Let's assume that a CSV file "File.csv," such as the one below, is going to be copied to C:\Fileimport from an external job. This copying may happen any time between 6 AM and 9 AM.

Process.csv

ServerName,ServerType,ID
MSDEBox ,Test,12
Yukon,BetaTesting,23
PRODSQL,Production,24

Create table

Create tables in the database as shown below.

Use test
Go
CREATE TABLE [FIle] (
[ServerName] varchar (255) NULL, 
[ServerType] varchar (255) NULL, 
[ID] varchar (255) NULL )
go
CREATE TABLE [Logtable] (
[ID] int identity(1,1),
[Status] varchar (255) NULL,
[Date] datetime default getdate())

DTS Package

Create the DTS package to import the above CSV file to SQL Server.

Make sure you enable the check box for "First row has column names"

In the SQL task, "Logtable," copy and paste the below SQL command.

Click for larger image

In the ActiveX script "Rename File," copy and paste the below code. This will rename the file with the current month and day as suffix after the file has been imported.

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
Main = DTSTaskExecResult_Success
End Function

Create SQL Job "TEST" to execute this package as shown below:

Create job "File Watcher" as shown below:

Create Job step "File Watcher":

In the command window, copy and paste the below code.

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\File.csv'
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

Create Schedule

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

Click for larger image

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.

1

Skipped DTS - File not found

2/24/04 6:00

2

Skipped DTS - File not found

2/24/04 6:05

3

Skipped DTS - File not found

2/24/04 6:10

4

Skipped DTS - File not found

2/24/04 6:15

5

Imported

2/24/04 6:20

6

Skipped DTS - Already Imported

2/24/04 6:25

7

Skipped DTS - Already Imported

2/24/04 6:30

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

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved