June 30, 2002 at 12:00 am
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
July 6, 2002 at 9:27 pm
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
July 7, 2002 at 7:35 am
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
July 8, 2002 at 1:33 pm
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
July 8, 2002 at 5:17 pm
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
July 8, 2002 at 10:01 pm
very nice artcile and explains clearly the FileSystemobject usage and it susefulness
November 20, 2002 at 4:06 pm
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'
November 20, 2002 at 6:27 pm
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
November 21, 2002 at 8:20 am
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
November 22, 2002 at 4:26 am
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
November 22, 2002 at 7:49 am
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?
November 25, 2002 at 7:03 am
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