Using DTS to Detect and Process a File

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Bruce Szabo » Using DTS to Detect and Process a File

12 posts, Page 2 of 2

««1 2

Using DTS to Detect and Process a File

Rate Topic

Display Mode

Topic Options

Author

Message

bsluman

Posted Friday, November 22, 2002 7:49 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, July 05, 2007 5:42 AM
Points: 31, Visits: 1

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?

Post #36637

AJ Ahrens

Posted Monday, November 25, 2002 7:03 AM

SSCommitted

Group: General Forum Members
Last Login: Friday, July 22, 2011 5:35 AM
Points: 1,758, Visits: 9

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

Post #36638

« Prev Topic | Next Topic »

12 posts, Page 2 of 2

««1 2

Permissions