Using DTS to Detect and Process a File

Introduction

There is nothing better than when a new technology lends itself to solving an old problem; well, there may be
some things better, but this will have to do for now. One of the classic
problems in computing — classic being defined as a problem I have seen before — is
processing files that were uploaded to a server. This problem arose in a recent situation and DTS was used to help load an uploaded file
into SQL server.

In order to process the file the first step is to be able to detect the arrival of the file via FTP. Once this has been accomplished the file needs to be unzipped and then loaded into SQL server. This process occurs each night and there may be more than one file on a nightly bases. The loading of a flat file to SQL server can be handled by a seperate DTS package, but the more difficult task is detecting and unzipping 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 by DTS. DTS can be used to perform transformations between different database formats and to combine fields, and the ability to run ActiveX scripts in the middle of the DTS process allows for additional operations like logging and file manipulation.

Check for toImport.txt

The initial part of the DTS process checks for a file created during a previous execution of this DTS package. In creating
a package that can run continuously, it is important to check to see if the process has failed previously. In this case, if the file that was to be imported into the SQL server still exists, the previous DTS package failed to properly process
and a new process should not start.

The script to check for the toImport.txt file follows.
Line 5 of this script instantiates a file system object. This object gives access to the file
system to look for files and to open a log file. Lines 6-10 set constants for directories and files
that will be used in the following scripts. If the constants are not used in the current script
they are there for reference as they are used in subsequent scripts.
The key directory in the toImport.txt check script
is the strPublishDir. This is the directory where the file toImport.txt is created, so
a seperate DTS package can use this file to load an SQL table.

Line 16 checks to see if a log file currently exists. If the file exists then
a file system object is opened to append text (line 21); otherwise, a new file is created (line 17).
Lines 18, 19, 21, 22, and 23 write text to the log file. The text varies depending on if the file was
created or opened.

Line 27 instantiates another file system object. Line 30 checks for the existence of
the toImport.txt file. If the file exists then a note is made in the log file
and the function main returns a failure (DTSTaskExecResult_Failure). If the file does not exist
then line 35 returns a success (DTSTaskExecResult_Success). Line 37 writes a message
in the log file, and it is closed on line 38. Lines 40-43 release the objects that were
created and the function ends.

toImport.txt Check Script







1 Function Main()
2 ‘
3 ‘ Create needed FileSystemObject and setup the directories for the file locations.
4 ‘
5 Set objFSO = CreateObject(“Scripting.FileSystemObject”)
6 strLogFile = “D:LogobjPartsLog.txt”
7 strFTPDir = “C:inetpubftproot”
8 strLandingDir = “D:Landing”
9 strPublishDir = “D:Publish”
10 strZipArchiveDir = “D:Archive”
11
12 ‘
13 ‘ Check to see if a log file exists if it does open it. If not create it.
14 ‘
15
16 If not objFSO.FileExists(strLogFile) then
17 Set master = objfso.CreateTextFile(strLogFile)
18 master.WriteLine “File Created @ ” & now
19 master.WriteLine ” In toImport Check Routine @ ” & now
20 Else
21 Set master = objFSO.OpenTextFile(strLogFile,8)
22 master.WriteLine
23 master.WriteLine “File Opened @ ” & now
24 master.WriteLine ” In toImport Check Routine @ ” & now
25 End if
26
27 Set fso = CreateObject(“Scripting.FileSystemObject”)
28
29 ‘
30 If fso.FileExists(strPublishDir & “toImport.txt”) then
31 master.WriteLine “toImport.txt EXISTS it should not exist at this point @ ” & now
32
33 Main = DTSTaskExecResult_Failure
34 Else
35 Main = DTSTaskExecResult_Success
36 End if
37 master.WriteLine “FILE CLOSED @” & now
38 master.close
39
40 set fso = nothing
41 set objfso = nothing
42 set master = nothing
43 set objfsoFolder = nothing
44
45 End Function

Check for .DONE file

Another beauty of DTS is that the movement from DTS object to DTS object within a package can be governed on
the success or failure of the precious step. In this case, the missing toImport.txt
file is a success. After this success, the files needed to be processed can be detected. In this
case, two files are uploaded. The first file is a zipped file with a .zip extention and another file
with a .done extension. The filename before the period is the same in both cases. This makes
the job of the next script to detect the .DONE file and unzip the compressed file.

Lines 5-25 are the same as the previous script. The lines set up the needed variables
and open the log file for writing. On line 27 a file system object is created
to get the files in the FTP directory. Line 28 starts a loop through the files in the
FTP folder. On line 29 the file suffix is tested to see if the .DONE file has arrived. If it
has arrived the filename is extracted without the extension, and the detection is logged on lines 31 and 32.
Line 35 instantiates a file system object that is used in line 37 to make sure the zip file has arrived. Lines 38 and 39
log this result to the log file.

The first thing done if the file has arrived is to copy it to an archive directory (line 43) and note it
in the log file (lines 44 – 48). Once the file has arrived and been backed up, it can then be unzipped.
Line 52 instantiates a shell object. Line 53 creates a command line that is run on line 54.
This results in the extraction of a .TXT file from the .ZIP file that has the same filename prefix as the .DONE and .Zip files.

In order to DTS the flat file into SQL, programatically, the file resides with the same name and in the same location
each time the process runs. Line 60 copies the .TXT file to the publishing directory and
renames the file to toImport.txt. The results are logged on line 61. The files used to create the toImport.txt
file are deleted on lines 65,67 and 69. Entries are made in the log file on lines 66,68, and
70. Line 71 returns the fact that the process has succeded.

Line 73 and 74 are used to log
the lack of a .DONE and return a DTS failure if a .DONE file does not exist in the FTP directory.
Line 76 and 77 log and then close the log file.
Lines 79-82 release the objects used in this function.







1 Function Main()
2 ‘
3 ‘ Create needed FileSystemObject and setup the directories for the file locations.
4 ‘
5 Set objFSO = CreateObject(“Scripting.FileSystemObject”)
6 strLogFile = “D:LogobjPartsLog.txt”
7 strFTPDir = “C:inetpubftproot”
8 strLandingDir = “D:Landing”
9 strPublishDir = “D:Publish”
10 strZipArchiveDir = “D:Archive”
11
12 ‘
13 ‘ Check to see if a log file exists if it does open it. If not create it.
14 ‘
15
16 If not objFSO.FileExists(strLogFile) then
17 Set master = objfso.CreateTextFile(strLogFile)
18 master.WriteLine “File Created @ ” & now
19 master.WriteLine ” In LAST File Exists Routine @ ” & now
20 Else
21 Set master = objFSO.OpenTextFile(strLogFile,8)
22 master.WriteLine
23 master.WriteLine “File Opened @ ” & now
24 master.WriteLine ” In LAST File Exists Routine @ ” & now
25 End if
26
27 Set objFSOFolder = objfso.getfolder(strFTPDir)
28 for each objFSOFile in objfsofolder.files
29 if ucase(right(objFSOFile.name,4)) = “DONE” then
30 strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-5))
31 master.WriteLine objFSOFile.name & ” detected @ ” & now
32 master.WriteLine strFileNameBase & ” BASENAME ”
33 end if
34 next
35 Set fso = CreateObject(“Scripting.FileSystemObject”)
36
37 If fso.FileExists(strFTPDir & “” & strFileNameBase & “.zip”) then
38 master.WriteLine strFileNameBase & ” .zip exists @” & now
39 master.WriteLine strFileNameBase & ” *********** LAST FILE EXISTS ***** SUCCESS******”
40 ‘
41 ‘ Move File to the Archive Directory
42 ‘
43 fso.copyfile strFTPDir & “” & strFileNameBase & “.zip”, strZipArchiveDir &
“” & strFileNameBase & “.zip”,true
44 If fso.FileExists(strZipArchiveDir & “” & strFileNameBase & “.zip”) then
45 master.WriteLine strFileNameBase & ” .zip was moved to the Archive Folder @” & now
46 else
47 master.WriteLine strFileNameBase & ” .zip move to the Archive Folder FAILED @ ” & now
48 end if
49 ‘
50 ‘ Unzip the file to the Publish Directory
51 ‘
52 Set WshShell = CreateObject(“WScript.Shell”)
53 strUnzip = “D:landingpkzipc.exe -extract -over=all ” & strFTPDir & “”
& strFileNameBase & “.zip ” & strPublishDir
54 WshShell.Run strUnZip,,true
55 master.WriteLine strFileNameBase & ” .txt was created in the Publish Folder @” & now
56 Set WshShell = nothing
57 ‘
58 ‘ Copy the file to a file named toImport.txt
59 ‘
60 fso.copyfile strPublishDir & “” & strFileNameBase &
“.txt”, strPublishDir & “toImport.txt”,true
61 master.WriteLine ” ” & strFileNameBase &
“.txt was copied to toImport.txt in the Publish Folder @” & now
62 ‘
63 ‘Clean Up Old Files and signal success for process
64 ‘
65 fso.DeleteFile strFTPDir & “” & trim(strFileNameBase) & “.done”
66 master.WriteLine ” ” & strFileNameBase & “.last was Deleted @” & now
67 fso.DeleteFile strFTPDir & “” & trim(strFileNameBase) & “.zip”
68 master.WriteLine ” ” & strFileNameBase & “.zip was Deleted @” & now
69 fso.DeleteFile strPublishDir & “” & trim(strFileNameBase) & “.txt”
70 master.WriteLine ” ” & strFileNameBase & “.txt was Deleted @” & now
71 Main = DTSTaskExecResult_Success
72 Else
73 master.WriteLine strFileNameBase & ” *********** LAST FILE EXISTS *****FAILURE******”
74 Main = DTSTaskExecResult_Failure
75 End if
76 master.WriteLine “FILE CLOSED @” & now
77 master.close
78
79 set fso = nothing
80 set objfso = nothing
81 set master = nothing
82 set objfsoFolder = nothing
83
84 End Function

Run another DTS Process

Once it has been verified that the toImport.txt file exists, the DTS package launches
another DTS package. In this case the DTS package loads the toImport.txt file into
SQL server. It would be possible to have any DTS package run at this point.

Delete toImport.txt

In order for this process to run continuously it is important to delete the toImport.txt file.
This is done with the following function. This function uses the techniques used in
the above scripts. The existence of the toImport.txt is checked, and if it exists it is deleted.







1 Function Main()
2 ‘
3 ‘ Create needed FileSystemObject and setup the directories for the file locations.
4 ‘
5 Set objFSO = CreateObject(“Scripting.FileSystemObject”)
6 strLogFile = “D:LogobjPartsLog.txt”
7 strFTPDir = “C:inetpubftproot”
8 strLandingDir = “D:Landing”
9 strPublishDir = “D:Publish”
10 strZipArchiveDir = “D:Archive”
11
12 ‘
13 ‘ Check to see if a log file exists if it does open it. If not create it.
14 ‘
15
16 If not objFSO.FileExists(strLogFile) then
17 Set master = objfso.CreateTextFile(strLogFile)
18 master.WriteLine “File Created @ ” & now
19 master.WriteLine ” In toImport Delete Routine @ ” & now
20 Else
21 Set master = objFSO.OpenTextFile(strLogFile,8)
22 master.WriteLine
23 master.WriteLine “File Opened @ ” & now
24 master.WriteLine ” In toImport Delete Routine @ ” & now
25 End if
26
27 Set fso = CreateObject(“Scripting.FileSystemObject”)
28
29 ‘
30 If fso.FileExists(strPublishDir & “toImport.txt”) then
31 master.WriteLine ” toImport.TXT deleted @ ” & now
32 fso.DeleteFile strPublishDir & “toImport.txt”
33 Main = DTSTaskExecResult_Success
34 Else
35 Main = DTSTaskExecResult_Failure
36 End if
37 master.WriteLine “FILE CLOSED @” & now
38 master.close
39
40 set fso = nothing
41 set objfso = nothing
42 set master = nothing
43 set objfsoFolder = nothing
44
45 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.


»


See All Articles by Columnist
Bruce Szabo

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles