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.
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