Using DTS to Detect and Process a File

,

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.

Rate

5 (1)

Share

Share

Rate

5 (1)