Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Jan 31, 2002

Using DTS to Detect and Process a File

By Bruce Szabo

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:\Log\objPartsLog.txt"
7    strFTPDir = "C:\inetpub\ftproot"
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:\Log\objPartsLog.txt"
7    strFTPDir = "C:\inetpub\ftproot"
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:\landing\pkzipc.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:\Log\objPartsLog.txt"
7    strFTPDir = "C:\inetpub\ftproot"
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




MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date