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.

Using DTS to Detect and Process a File

Using DTS to Detect and Process a File

Introduction

Getting Started

File Arrival Check

toImport.txt Check Script

Marker File

Run another DTS Process

Remove File

Conclusions

Rate

Share

Categories

Share

Rate

Using DTS to Detect and Process a File

Using DTS to Detect and Process a File

Introduction

Getting Started

File Arrival Check

toImport.txt Check Script

Marker File

Run another DTS Process

Remove File

Conclusions

Rate

Share

Categories

Share

Rate

Related content

Save Your Packages Locally

Professional SQL Server 2000 DTS Review

Scrubbing Data With DTS : Part 1

Using FileSystemObject with DTS

Using the Dynamic Properties Task

Cookies on SQLServerCentral