"File Watcher" for SQL Server

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