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.WriteLineobjFSOFile.name & " detected @ " & now master.WriteLinestrFileNameBase & " BASENAME " end if next Set fso = CreateObject("Scripting.FileSystemObject") If fso.FileExists(strFTPDir & "\" & strFileNameBase & ".zip") then master.WriteLinestrFileNameBase & " .zip exists @" & now master.WriteLinestrFileNameBase & " *********** 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.WriteLinestrFileNameBase & " .zip was moved to the Archive Folder @" & now else master.WriteLinestrFileNameBase & " .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.WriteLinestrFileNameBase & " .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.WriteLinestrFileNameBase & " *********** 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.