SQLServerCentral / Discuss Content Posted by Bruce Szabo / Article Discussions / Article Discussions by Author / Using DTS to Detect and Process a File / Latest Posts

RE: Using DTS to Detect and Process a File

AJ Ahrens — Mon, 25 Nov 2002 07:03:00 GMT

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 ExplicitGlobal bRanToday As BooleanGlobal sServerName As StringGlobal sConn As StringGlobal iJobId As IntegerSub 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 commentsDim sFile As StringDim adoConn As ADODB.ConnectionDim adoCmd As ADODB.CommandDim iFound As IntegerDim clsCheck As New clsCheckIfRanTodayDim sTest As StringDim sFilePath As StringOn 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 WithCleanUp: 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 SubErrorTrap: 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 performedEnd Sub-- //////////////////////////////////////////////////////////-- Class module-- //////////////////////////////////////////////////////////Private Function fDailyAlreadyProcessedToday()Dim adoCon As ADODB.ConnectionDim adoCom As ADODB.CommandDim 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 IfCleaner: 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 connectionEnd FunctionPrivate Sub Class_Initialize() fDailyAlreadyProcessedToday '' Runs PRIVATE function aboveEnd SubAJ AhrensSQL DBACustom Billing AT&T Labs

RE: Using DTS to Detect and Process a File

bsluman — Fri, 22 Nov 2002 07:49:00 GMT

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?

RE: Using DTS to Detect and Process a File

bmsjr — Fri, 22 Nov 2002 04:26:00 GMT

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?BruceBruce Szabo, MCSE+I, MCDBA, MCSD

RE: Using DTS to Detect and Process a File

bsluman — Thu, 21 Nov 2002 08:20:00 GMT

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.DONEWe 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

RE: Using DTS to Detect and Process a File

bmsjr — Wed, 20 Nov 2002 18:27:00 GMT

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

RE: Using DTS to Detect and Process a File

bsluman — Wed, 20 Nov 2002 16:06:00 GMT

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.nameThe error is:Object Required: 'objFSOFile'

RE: Using DTS to Detect and Process a File

sreeram68 — Mon, 08 Jul 2002 22:01:00 GMT

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

RE: Using DTS to Detect and Process a File

bmsjr — Mon, 08 Jul 2002 17:17:00 GMT

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. BruceBruce Szabo, MCSE+I, MCDBA, MCSD

RE: Using DTS to Detect and Process a File

Steve Jones - SSC Editor — Mon, 08 Jul 2002 13:33:00 GMT

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 Jonessjones@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/sjones

RE: Using DTS to Detect and Process a File

bmsjr — Sun, 07 Jul 2002 07:35:00 GMT

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. BruceBruce Szabo, MCSE+I, MCDBA, MCSD

RE: Using DTS to Detect and Process a File

Andy Warren — Sat, 06 Jul 2002 21:27:00 GMT

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!Andyhttp://www.sqlservercentral.com/columnists/awarren/

Using DTS to Detect and Process a File

bmsjr — Sun, 30 Jun 2002 00:00:00 GMT

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