Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using DTS to Detect and Process a File


Using DTS to Detect and Process a File

Author
Message
bsluman
bsluman
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 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?



AJ Ahrens
AJ Ahrens
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1812 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search