Using DTS to Detect and Process a File

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bszabo/dtsfilearrival.asp


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • Good use of the wshell object. This is cleaner than the "shell" command used in VB which normally runs asynchronously. I see in a couple places you're declaring more than one instance of the file system object...why? Also, I think you're using the DONE file as an indicator that the copy of the zip file to the folder is complete?

    Note to Readers: Pkzipc is the command line version of PKzip and is not installed on your server by default!

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • Confusion was the only reason I had more than one instance of the filesystemobject. It was poor programming on my part and I missed it in the original article.

    You are correct, the .DONE file was used as a marker file to signal the transfer of the .ZIP file was complete.

    Another good point about pkzip being the command line utility. You can purchase it from pkware the url is http://www.pkware.com.

    Bruce

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • Nice article, however I think some of the lesser experienced might like some more commentary on how it works. For me, however, its nice to see how someone else does this.

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • Thanks Steve, I am always accused of being too long winded and complex in my e-mails. 🙂 I try to present some scripts that I find useful and do a little more annotation without getting too involved. I will keep it in mind in the future.

    Bruce

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • very nice artcile and explains clearly the FileSystemobject usage and it susefulness

  • I am having problems with this procedure. I get an error on line 28 of the "Check for .DONE file" component. Where would I find this .DONE file? The line it is erroring on is:

    master.WriteLine objFSOFile.name

    The error is:

    Object Required: 'objFSOFile'

  • Have you modified the code at all? The .Done file is going to be located in the ftp directory. If you right click on the DTS package one of the global variables specifies the ftp directory.

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • Ok, I am looking at the .DONE component and I think that I might see something wrong with it. Correct me if I am wrong:

    In line 33 or so you check for the ".DONE" tagged to the end of the file.

    if ucase(right(objFSOFile.name,4)) = "DONE" then strFileNameBase = trim(left(objFSOFile.Name, len(objFSOFile.name)-5))

    So, say we have a file named 20021212.zip.DONE. I am assuming that this is what you are looking for. (I still do not know how the .DONE gets added in the first place)

    So, you set the variable filename base equal to the file name minus 5 which effectively eliminates the .DONE.

    This gives us 20021212.zip as the filenamebase.

    But, on line 41 you write the following:

    If fso.FileExists(strFTPDir & "\" & strFileNameBase & ".zip")

    This file will not exist because it is still called:20021212.zip.DONE

    We have not removed the .DONE from the actual filename. Even if we had then the .zip is still there and we would be looking for 20021212.zip.zip

  • Now I see your issue. 🙂 I have the sending company send me two files. A large .Zip file and a small .DONE file. I do not and can not start processing the .Zip file untill it is fully uploaded. For this reason the sending company sends me a .DONE file to signal the first file copy is complete. I have FILE1.DONE and FILE1.ZIP. I really only needed to know the ZIP arrived so I extract the base filename from the .DONE file by subtracting the 5 characters. I must not have mentioned the two file system I have. Sorry. Does that help?

    Bruce

    Bruce Szabo, MCSE+I, MCDBA, MCSD


    Bruce Szabo, MCSE+I, MCDBA, MCSD

  • Yes, quite a help. Actually I figured it out yesterday. It is working nicely now. Do you have the company send you the DONE file only after the percieve that the zip file is done uploading?

  • I use a similar process for detection of a .DONE file. I have built a VB6 exe and fire a DTS package every 2 hours from 7:30 am > 9:30 PM looking for the file. I write into a SQL table JobId, SearchDateTime, FoundFileYN. Based on IF the file exists and IF this is the first time today (AND/OR IF this is first run ever) inside a stored-procedure I then fire off the job that processes the file.

    I use an external VB6 executable to keep this aspect separate from SQL and if modifications are ever needed they are modified external from DTS modifications.

    Here is my VB6 code:

    -- //////////////////////////////////////////////////////////

    Option Explicit

    Global bRanToday As Boolean

    Global sServerName As String

    Global sConn As String

    Global iJobId As Integer

    Sub Main()

    '' Created: 10/16/2002

    '' Created By: AJ Ahrens - SQL DBA AT&T Labs x3375

    '' Location: K$\root of servers

    '' Purpose: Provide small efficient way of checking for DONE file to start _

    Daily DISP process

    '' --

    '' Modified: 10/21/2002

    '' Modified By: AJ Ahrens - SQL DBA AT&T Labs x3375

    '' Location: SAB

    '' Purpose: Provide comments

    Dim sFile As String

    Dim adoConn As ADODB.Connection

    Dim adoCmd As ADODB.Command

    Dim iFound As Integer

    Dim clsCheck As New clsCheckIfRanToday

    Dim sTest As String

    Dim sFilePath As String

    On Error GoTo ErrorTrap

    iFound = 0 '' Default to state that DONE file has not been found

    iJobId = 1 '' Daily Disp Job Id

    sFilePath = "\Inetpub\ftproot\P2C\P2Cany.ftp.done"

    Select Case Environ$("ComputerName") '' Part of internal functions that determines _

    PC/Server name

    Case Is = "DISP2", "PLB1", "DISP5"

    sFile = Dir("K:" & sFilePath) '' Path to where DONE file should be

    sServerName = Environ$("ComputerName")

    Case Else

    sFile = Dir("\\164.120.88.81\K$" & sFilePath)

    '' Default if not ran from Server is to be DISP2 _

    IP address

    sServerName = "164.120.88.81" '' Default Server name to be DISP2 IP address

    End Select

    sConn = "Driver={SQL Server};Server=" & sServerName & ";db=P2C;trusted_connection=true"

    '' Creates connection string to be used for ADO _

    connections

    clsCheck '' Class module that checks to see if process _

    already ran today

    If bRanToday = True Then Exit Sub '' If process has already ran get out without _

    updating Search log

    If sFile <> "" Then '' Code to determine IF file found update _

    flag to state same

    iFound = -1

    End If

    Set adoConn = New ADODB.Connection '' Performs late-binding routine

    With adoConn '' Opens ADO connection to server

    .ConnectionString = sConn

    .CommandTimeout = 0

    .Open

    End With

    Set adoCmd = New ADODB.Command '' performs late-binding routine

    With adoCmd '' Opens ADO command to INSERT search attempt

    .ActiveConnection = adoConn '' establishes what OPEN ADO connection to use

    .CommandType = adCmdText '' Determines what type of command to perform

    .CommandText = "INSERT INTO P2C.dbo.tblDailySearchLog (SearchDateTime, JobId, FoundYN) " & _

    "VALUES (GetDate(), " & iJobId & ", " & iFound & ")"

    '' SQL command string here

    .Execute '' Tells system to perform SQL command

    End With

    CleanUp:

    Set adoCmd = Nothing '' Destroys ADO Command object (There is no _

    CLOSE option)

    If adoConn.State = adStateOpen Then '' Checks to ensure the ADO Connection is _

    OPEN before CLOSE

    adoConn.Close

    End If

    Set adoConn = Nothing '' Destroys ADO Connection regardless of _

    above code

    Exit Sub

    ErrorTrap:

    If Err.Number = 438 Then '' Isolates non-critical error from error-trap

    Err.Clear

    Resume Next

    Else

    MsgBox "Error: " + Str(Err.Number) + " Description: " + Err.Description

    End If

    GoTo CleanUp '' Ensures that object clean-up is performed

    End Sub

    -- //////////////////////////////////////////////////////////

    -- Class module

    -- //////////////////////////////////////////////////////////

    Private Function fDailyAlreadyProcessedToday()

    Dim adoCon As ADODB.Connection

    Dim adoCom As ADODB.Command

    Dim adoRs As ADODB.Recordset

    Set adoCon = New ADODB.Connection '' Performs late-binding routine

    With adoCon '' Opens ADO connection with connection string from _

    SUB MAIN()

    .ConnectionString = sConn

    .CommandTimeout = 0

    .Open

    End With

    Set adoCom = New ADODB.Command '' Performs late-binding routine

    With adoCom '' Prepares ADO command object with Connection _

    and SQL command

    .CommandText = "SELECT MAX(CONVERT(VARCHAR, LastRunDateTime, 101)) " & _

    "FROM P2C.dbo.tblDailyLog WHERE JobId = " & iJobId

    .CommandType = adCmdText

    .ActiveConnection = adoCon

    End With

    Set adoRs = New ADODB.Recordset '' Performs late-binding routine

    With adoRs '' Establishes cursor at CLIENT-SIDE in case _

    record count req'd

    .CursorLocation = adUseClient

    End With

    Set adoRs = adoCom.Execute '' Opens ADO redordset by EXECUTE of _

    ADO command object

    If "'" & adoRs.Fields(0) & "'" = "''" Then

    bRanToday = False '' Routine to ensure program doesn't blow

    GoTo Cleaner

    Exit Function '' up in case 1st time run

    End If

    If CDate(adoRs.Fields(0)) = Date Then '' Checks ADO Recordset info to determine _

    if process ran today

    bRanToday = True '' If process ran today/ or not flags as such

    Else

    bRanToday = False

    End If

    Cleaner:

    If adoRs.State = adStateOpen Then

    adoRs.Close '' Closes ADO recordset

    End If

    Set adoRs = Nothing '' Destroys ADO recordset

    Set adoCom = Nothing '' Destroys ADO command object

    If adoCon.State = adStateOpen Then

    adoCon.Close '' Closes ADO connection

    End If

    Set adoCon = Nothing '' Destroys ADO connection

    End Function

    Private Sub Class_Initialize()

    fDailyAlreadyProcessedToday '' Runs PRIVATE function above

    End Sub

    AJ Ahrens

    SQL DBA

    Custom Billing AT&T Labs



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply