Using DTS to Detect and Process a File
Introduction
There is nothing better when a new technology lends itself to an old problem. One of the classic
old problems in computing has been to process files that were uploaded to a server. As this problem arose
in a recent situation I was ablt to use DTS to help upload a file into SQL server.
In order to process the file the first step was to be able to detect the arrival of the
file via FTP. Once this had been accomplished the file needed to be loaded into SQL server.
The loading of a flat file to SQL server can be handled by a seperate DTS package but
the more difficult task was detecting, unzipping and starting the processing of the file.
Getting Started
The following is a diagram of what the final DTS package looks like in the DTS Design window. The package
is broken down into four parts. Three of the parts are ActiveX Script Tasks while the fourth launches another DTS Process.
Knowing and experimenting with the various objects one can use in the DTS process quickly expands the tasks that can be
accomplished. This was never as evident as working on this project. DTS had been used previouly to
perform transformations between different database formats and used to combine fields but
the ability to run ActiveX scripts in the middle of the DTS process allowed for some nice
logging and file manipulation.
File Arrival Check
The beginning of the DTS process checks for a file that is created during the DTS process. In creating
a process that can run continuously it is important to check to see if the process had failed
on a previous attempt. In this case if the file that was to be imported into the SQL server
still exists the DTS package failed to properly process on a previous run and the new processing should
not start.
The script to check for the import.txt file follows.
toImport.txt Check Script
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLogFile = "D:\Log\objPartsLog.txt"
strFTPDir = "C:\inetpub\ftproot"
strLandingDir = "D:\Landing"
strPublishDir = "D:\Publish"
strZipArchiveDir = "D:\Archive"
'
' Check to see if a log file exists if it does open it. If not create it.
'
If not objFSO.FileExists(strLogFile) then
Set master = objfso.CreateTextFile(strLogFile)
master.WriteLine "File Created @ " & now
master.WriteLine " In toImport Check Routine @ " & now
Else
Set master = objFSO.OpenTextFile(strLogFile,8)
master.WriteLine
master.WriteLine "File Opened @ " & now
master.WriteLine " In toImport Check Routine @ " & now
End if
Set fso = CreateObject("Scripting.FileSystemObject")
'
If fso.FileExists(strPublishDir & "\toImport.txt") then
master.WriteLine " toImport.txt EXISTS it should not exist at this point @ " & now
fso.DeleteFile strPublishDir & "\toImport.txt"
Main = DTSTaskExecResult_Failure
Else
Main = DTSTaskExecResult_Success
End if
master.WriteLine "FILE CLOSED @" & now
master.close
set fso = nothing
set objfso = nothing
set master = nothing
set objfsoFolder = nothing
End Function |
Marker File
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLogFile = "D:\Log\objPartsLog.txt"
strFTPDir = "C:\inetpub\ftproot"
strLandingDir = "D:\Landing"
strPublishDir = "D:\Publish"
strZipArchiveDir = "D:\Archive"
'
' Check to see if a log file exists if it does open it. If not create it.
'
If not objFSO.FileExists(strLogFile) then
Set master = objfso.CreateTextFile(strLogFile)
master.WriteLine "File Created @ " & now
master.WriteLine " In LAST File Exists Routine @ " & now
Else
Set master = objFSO.OpenTextFile(strLogFile,8)
master.WriteLine
master.WriteLine "File Opened @ " & now
master.WriteLine " In LAST File Exists Routine @ " & now
End if
Set objFSOFolder = objfso.getfolder(strFTPDir)
for each objFSOFile in objfsofolder.files
if ucase(right(objFSOFile.name,4)) = "MARK" then
strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-5))
master.WriteLine objFSOFile.name & " detected @ " & now
master.WriteLine strFileNameBase & " BASENAME "
end if
next
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(strFTPDir & "\" & strFileNameBase & ".zip") then
master.WriteLine strFileNameBase & " .zip exists @" & now
master.WriteLine strFileNameBase & " *********** LAST FILE EXISTS ***** SUCCESS******"
'
' Move File to the Archive Directory
'
fso.copyfile strFTPDir & "\" & strFileNameBase & ".zip", strZipArchiveDir & "\" & strFileNameBase & ".zip",true
If fso.FileExists(strZipArchiveDir & "\" & strFileNameBase & ".zip") then
master.WriteLine strFileNameBase & " .zip was moved to the Archive Folder @" & now
else
master.WriteLine strFileNameBase & " .zip move to the Archive Folder FAILED @ " & now
end if
'
' Unzip the file to the Publish Directory
'
Set WshShell = CreateObject("WScript.Shell")
strUnzip = "D:\Ford\landing\pkzipc.exe -extract -over=all " & strFTPDir & "\" & strFileNameBase & ".zip " & strPublishDir
WshShell.Run strUnZip,,true
master.WriteLine strFileNameBase & " .txt was created in the Publish Folder @" & now
Set WshShell = nothing
'
' Copy the file to a file named toImport.txt
'
fso.copyfile strPublishDir & "\" & strFileNameBase & ".txt", strPublishDir & "\toImport.txt",true
master.WriteLine " " & strFileNameBase & ".txt was copied to toImport.txt in the Publish Folder @" & now
'
'Clean Up Old Files and signal success for process
'
fso.DeleteFile strFTPDir & "\" & trim(strFileNameBase) & ".MARK"
master.WriteLine " " & strFileNameBase & ".last was Deleted @" & now
fso.DeleteFile strFTPDir & "\" & trim(strFileNameBase) & ".zip"
master.WriteLine " " & strFileNameBase & ".zip was Deleted @" & now
fso.DeleteFile strPublishDir & "\" & trim(strFileNameBase) & ".txt"
master.WriteLine " " & strFileNameBase & ".txt was Deleted @" & now
Main = DTSTaskExecResult_Success
Else
master.WriteLine strFileNameBase & " *********** LAST FILE EXISTS *****FAILURE******"
Main = DTSTaskExecResult_Failure
End if
master.WriteLine "FILE CLOSED @" & now
master.close
set fso = nothing
set objfso = nothing
set master = nothing
set objfsoFolder = nothing
End Function |
Run another DTS Process
Remove File
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
'
' Create needed FileSystemObject and setup the directories for the file locations.
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
strLogFile = "D:\Log\objPartsLog.txt"
strFTPDir = "C:\inetpub\ftproot"
strLandingDir = "D:\Landing"
strPublishDir = "D:\Publish"
strZipArchiveDir = "D:\Archive"
'
' Check to see if a log file exists if it does open it. If not create it.
'
If not objFSO.FileExists(strLogFile) then
Set master = objfso.CreateTextFile(strLogFile)
master.WriteLine "File Created @ " & now
master.WriteLine " In toImport Delete Routine @ " & now
Else
Set master = objFSO.OpenTextFile(strLogFile,8)
master.WriteLine
master.WriteLine "File Opened @ " & now
master.WriteLine " In toImport Delete Routine @ " & now
End if
Set fso = CreateObject("Scripting.FileSystemObject")
'
If fso.FileExists(strPublishDir & "\toImport.txt") then
master.WriteLine " toImport.TXT deleted @ " & now
fso.DeleteFile strPublishDir & "\toImport.txt"
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End if
master.WriteLine "FILE CLOSED @" & now
master.close
set fso = nothing
set objfso = nothing
set master = nothing
set objfsoFolder = nothing
End Function |
Conclusions
A DTS package is explored and the scripts used to detect a file arrival are
explained. This process is used to load a file into SQL server but the power
of SQL server allows this process to be used for more than just loading data
into SQL server.
