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())