Loading a Series of Flat Files

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

    .

  • Good article. Always like articles with code that I can play with

    One comment I would make though is that I'd eliminate the GOTO's by using positive logic. eg:

    
    
    IF @rtn = 0
    begin
    do something
    if @rtn = 0
    begin
    do something more
    end
    else
    begin
    flag error
    end
    end
    else
    begin
    flag error
    end

    Also, is it my browser settings or your formatting that double spaces all the lines?

    Edited by - phillcart on 10/31/2002 02:39:37 AM

    --------------------
    Colt 45 - the original point and click interface

  • Good article - I agree that it's good to see the code as well. Perhaps it ought to be downloadable, and have a version with your error checking as this is very important.

    It would be interesting to write a VB app, using ADO to load the data, to see which would be fastest.

    Personally I would use VB, but I'm a little biased !

    thanks

    John, UK

  • Thanks for your comments - some answers: -

    I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.

    I think the double space formatting is my fault!

    With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.

    Regards,

    Andy Jones

    .

  • My biggest project of the last 2 years has been a process that automatically logs and imports files that are uploaded to our server from stores. This process handles both transaction and customer files from 4 different POS systems, each with their own file format. The files also come compressed/archived in .ZIP, .Tar, or .tar.Z formats. (I skipped a lot of details, it gets even more 'interesting'.)

    For my solution, I used 3 DTS packages. The first one has an ActiveX script that uses the file system object to loop through the files in the directory, logs any new ones, and performs any extraction that is necessary. This happens every 5 minutes.

    The second package, which is executed every 30 minutes, goes through the log (a table) searching for files that haven't imported, and calls the third package for each file that needs to be imported, passing the filename via a global variable.

    That's the basics of the process. I personally like the DTS method better because of flexibility, error handling capability, and logging. Whenever an error occurs, I get an email telling me where it occurred, and if it happens in an ActiveX script, the email includes the line of code where the error occurred. I also prefer to keep my DTS packages in SQL Server, its simpler to access them, and they're always backed up.

    One final advantage to the way I do it, everything I use is documented. May not be well-documented, but at least there's something there. You want to be careful about using undocumented stuff, especially since it may not always be there.

    Anyway, there's my 2-cents worth. If anyone is interested in more information, I'll be happy to provide. My process works; that's gotta be worth something.

    James C Loesch


    James C Loesch

  • quote:


    Thanks for your comments - some answers: -

    I used goto labels because my original solution required the logging of a specific error message and then a general one e.g. 'Load of <file> failed at <Datatime>' so I used the goto label to keep all logging in the same place.

    I think the double space formatting is my fault!

    With regard to the speed of load, I have found the data pump task to be extremely efficient when loading files with a large number of records, although I havn't got any comparison figures with other methods.

    Regards,

    Andy Jones


    You can still keep the error logging in one place. In my example where I've said flag error this could set an error flag and/or build an error message. Then at the end of the proc you'd just check to see if an error occurred and then log it.

    I had a bad experience with goto's as a child and have been mortally afraid of them ever since

    Thanks

    Phill Carter

    Edited by - phillcart on 10/31/2002 4:11:12 PM

    --------------------
    Colt 45 - the original point and click interface

  • Interesting solution. Thanks for bringing those undocumented xp's to light. 🙂

    Personally, I'd do a couple things different. It's probably the programmer in me, but I'd replace sp_GetFiles, and sp_FolderFileExist with functions. Then instead of working with ##Temp tables, your curser would be based on

    select * from dbo.GetFiles(@myfolder)

    I'd also use BCP instead of DTS to load the files. Again, personal preference.

    (Johnhind, I'd put money on dts bulk load or bcp over any vb solution.)

    Thanks again,

    John

  • Good point about using a UDF. With regard to using BCP, my original requirement used files which didn't have a consistent number of fields on each line e.g.: -

    1,2

    1,2,3

    1

    etc...

    I couldn't get BCP / Bulk insert to work with these types of files, so that was the reason behind using DTS.

    Thanks for the reply.

    Regards,

    Andy Jones

    .

  • Andy,

    Must be fixed width fields, otherwise you could spedify the delimeter in the BCP command. In the case of fixed width files, you could bcp into a generic table:

    create table Import

    ( id int identity primary key,

    data varchar(4000)

    )

    Then parse the data field as needed in sql. Just an option.

    John

  • I have been doing this for years with stored procs, BCP, and a few control tables. Multiple Import sources, multiple import structures within the same file, going to multiple tables determined by structure of data line.

    I was very interested in seeing it done using DTS, excellent article....

  • I am currently doing this same thing in SQL 7 and SQL 2K. Please read whole article.

    Our older process uses DTS and BCP to load data and process it. This process also uses TEMP tables (I know BAD BAD BAD) I have rewritten that particular piece of the code.

    I have created several different VB apps that look for the text files and write a record into SQL (ADO using stored-procedure). If the file is found it writes a flag stating TRUE else FALSE.

    I then have the DTS packages scheduled to run around each other that look in the log to see if the file is there, if we have processed today, etc.. If all conditions are TRUE then we perform the INSERT.

    I have created a LINKED SERVER to handle the text files. I have also created a schema.ini file to handle column names and data types. This way SQL handles ALL processing.

    The old process ran on average 1 hour 30 to 1 hour 50 minutes. Using the LINKED SERVER and with more validations the process takes 5 minutes 20 seconds.

    just a different way of looking at the issue.

    I can post sample code if you would like to look at it.



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • I think we would be interested to see how you do it !

    John

  • quote:


    I think we would be interested to see how you do it !

    John


    John,

    please find the code below. I have provided the VB6 code, the DTS package, and a couple of stored-procedures. This is a lot of data to weed through and may help you. Please note that you can command line extracts for .tar, .Z, .tar.Z.

    If I have missed anything please let me know and I will provide it to you.

    Thanks,

    AJ Ahrens

    ////////////////////////////

    // Code to create LINKED SERVER

    exec sp_addlinkedserver

    @server = 'txtSrvr',

    @provider = 'Microsoft.Jet.OLEDB.4.0',

    @srvproduct = 'OLE DB Provider for Jet (Text IISAM)',

    @datasrc = 'c:\MyDir\',

    @provstr = 'Text'

    go

    exec sp_addlinkedsrvlogin 'txtSrvr', false, NULL, 'admin'

    go

    --SELECT * FROM [txtSrvr]...[TextFile#txt]

    go

    // Code for DTS package to determine if daily process ready

    '****************************************************************

    'Microsoft SQL Server 2000

    'Visual Basic file generated for DTS Package

    'File Name: C:\Documents and Settings\aahrens.GBSNT001\Desktop\Daily Disp Process Loader.bas

    'Package Name: Daily Disp Process Loader

    'Package Description:

    'Generated Date: 11/12/2002

    'Generated Time: 9:24:28 AM

    '****************************************************************

    Option Explicit

    Public goPackageOld As New DTS.Package

    Public goPackage As DTS.Package2

    Private Sub Main()

    Set goPackage = goPackageOld

    goPackage.Name = "Daily Disp Process Loader"

    goPackage.WriteCompletionStatusToNTEventLog = False

    goPackage.FailOnError = False

    goPackage.PackagePriorityClass = 2

    goPackage.MaxConcurrentSteps = 4

    goPackage.LineageOptions = 0

    goPackage.UseTransaction = True

    goPackage.TransactionIsolationLevel = 4096

    goPackage.AutoCommitTransaction = True

    goPackage.RepositoryMetadataOptions = 0

    goPackage.UseOLEDBServiceComponents = True

    goPackage.LogToSQLServer = False

    goPackage.LogServerFlags = 0

    goPackage.FailPackageOnLogFailure = False

    goPackage.ExplicitGlobalVariables = False

    goPackage.PackageType = 0

    '---------------------------------------------------------------------------

    ' create package connection information

    '---------------------------------------------------------------------------

    Dim oConnection As DTS.Connection2

    '------------- a new connection defined below.

    'For security purposes, the password is never scripted

    Set oConnection = goPackage.Connections.New("SQLOLEDB.1")

    oConnection.ConnectionProperties("Integrated Security") = "SSPI"

    oConnection.ConnectionProperties("Persist Security Info") = True

    oConnection.ConnectionProperties("Initial Catalog") = "P2C"

    oConnection.ConnectionProperties("Data Source") = "(local)"

    oConnection.ConnectionProperties("Locale Identifier") = 1033

    oConnection.ConnectionProperties("Prompt") = 4

    oConnection.ConnectionProperties("General Timeout") = 0

    oConnection.ConnectionProperties("Use Procedure for Prepare") = 1

    oConnection.ConnectionProperties("Auto Translate") = True

    oConnection.ConnectionProperties("Packet Size") = 4096

    oConnection.ConnectionProperties("Application Name") = "DTS Designer"

    oConnection.ConnectionProperties("Workstation ID") = "PLB1"

    oConnection.Name = "PLB1"

    oConnection.ID = 1

    oConnection.Reusable = True

    oConnection.ConnectImmediate = False

    oConnection.DataSource = "(local)"

    oConnection.ConnectionTimeout = 0

    oConnection.Catalog = "P2C"

    oConnection.UseTrustedConnection = True

    oConnection.UseDSL = False

    'If you have a password for this connection, please uncomment and add your password below.

    'oConnection.Password = "<put the password here>"

    goPackage.Connections.Add oConnection

    Set oConnection = Nothing

    '---------------------------------------------------------------------------

    ' create package steps information

    '---------------------------------------------------------------------------

    Dim oStep As DTS.Step2

    Dim oPrecConstraint As DTS.PrecedenceConstraint

    '------------- a new step defined below

    Set oStep = goPackage.Steps.New

    oStep.Name = "DTSStep_DTSCreateProcessTask_1"

    oStep.Description = "Check for DONE file"

    oStep.ExecutionStatus = 1

    oStep.TaskName = "DTSTask_DTSCreateProcessTask_1"

    oStep.CommitSuccess = False

    oStep.RollbackFailure = False

    oStep.ScriptLanguage = "VBScript"

    oStep.AddGlobalVariables = True

    oStep.RelativePriority = 3

    oStep.CloseConnection = False

    oStep.ExecuteInMainThread = False

    oStep.IsPackageDSORowset = False

    oStep.JoinTransactionIfPresent = False

    oStep.DisableStep = False

    oStep.FailPackageOnError = False

    goPackage.Steps.Add oStep

    Set oStep = Nothing

    '------------- a new step defined below

    Set oStep = goPackage.Steps.New

    oStep.Name = "DTSStep_DTSExecuteSQLTask_1"

    oStep.Description = "Execute SQL Task: undefined"

    oStep.ExecutionStatus = 1

    oStep.TaskName = "DTSTask_DTSExecuteSQLTask_1"

    oStep.CommitSuccess = False

    oStep.RollbackFailure = False

    oStep.ScriptLanguage = "VBScript"

    oStep.AddGlobalVariables = True

    oStep.RelativePriority = 3

    oStep.CloseConnection = False

    oStep.ExecuteInMainThread = False

    oStep.IsPackageDSORowset = False

    oStep.JoinTransactionIfPresent = False

    oStep.DisableStep = False

    oStep.FailPackageOnError = False

    goPackage.Steps.Add oStep

    Set oStep = Nothing

    '------------- a precedence constraint for steps defined below

    Set oStep = goPackage.Steps("DTSStep_DTSExecuteSQLTask_1")

    Set oPrecConstraint = oStep.precedenceConstraints.New("DTSStep_DTSCreateProcessTask_1")

    oPrecConstraint.StepName = "DTSStep_DTSCreateProcessTask_1"

    oPrecConstraint.PrecedenceBasis = 1

    oPrecConstraint.Value = 0

    oStep.precedenceConstraints.Add oPrecConstraint

    Set oPrecConstraint = Nothing

    '---------------------------------------------------------------------------

    ' create package tasks information

    '---------------------------------------------------------------------------

    '------------- call Task_Sub1 for task DTSTask_DTSCreateProcessTask_1 (Check for DONE file)

    Call Task_Sub1(goPackage)

    '------------- call Task_Sub2 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)

    Call Task_Sub2(goPackage)

    '---------------------------------------------------------------------------

    ' Save or execute package

    '---------------------------------------------------------------------------

    'goPackage.SaveToSQLServer "(local)", "sa", ""

    goPackage.Execute

    goPackage.Uninitialize

    'to save a package instead of executing it, comment out the executing package line above and uncomment the saving

    package line

    Set goPackage = Nothing

    Set goPackageOld = Nothing

    End Sub

    '------------- define Task_Sub1 for task DTSTask_DTSCreateProcessTask_1 (Check for DONE file)

    Public Sub Task_Sub1(ByVal goPackage As Object)

    Dim oTask As DTS.Task

    Dim oLookup As DTS.Lookup

    Dim oCustomTask1 As DTS.CreateProcessTask2

    Set oTask = goPackage.Tasks.New("DTSCreateProcessTask")

    Set oCustomTask1 = oTask.CustomTask

    oCustomTask1.Name = "DTSTask_DTSCreateProcessTask_1"

    oCustomTask1.Description = "Check for DONE file"

    oCustomTask1.ProcessCommandLine = "K:\DONEFileCheck.exe"

    oCustomTask1.SuccessReturnCode = 0

    oCustomTask1.Timeout = 0

    oCustomTask1.TerminateProcessAfterTimeout = False

    oCustomTask1.FailPackageOnTimeout = True

    goPackage.Tasks.Add oTask

    Set oCustomTask1 = Nothing

    Set oTask = Nothing

    End Sub

    '------------- define Task_Sub2 for task DTSTask_DTSExecuteSQLTask_1 (Execute SQL Task: undefined)

    Public Sub Task_Sub2(ByVal goPackage As Object)

    Dim oTask As DTS.Task

    Dim oLookup As DTS.Lookup

    Dim oCustomTask2 As DTS.ExecuteSQLTask2

    Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")

    Set oCustomTask2 = oTask.CustomTask

    oCustomTask2.Name = "DTSTask_DTSExecuteSQLTask_1"

    oCustomTask2.Description = "Execute SQL Task: undefined"

    oCustomTask2.SQLStatement = "spCheckIfDailyRunReady"

    oCustomTask2.ConnectionID = 1

    oCustomTask2.CommandTimeout = 0

    oCustomTask2.OutputAsRecordset = False

    goPackage.Tasks.Add oTask

    Set oCustomTask2 = Nothing

    Set oTask = Nothing

    End Sub

    // CODE for stored-procedure that will fire off daily process if all requirements are TRUE

    CREATE PROCEDURE spCheckIfDailyRunReady

    /*

    Created:10/03/2002

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

    Purpose:Determine whether or not to process Daily DISP job

    */

    AS

    IF (SELECT MAX(CONVERT(VARCHAR, LastRunDateTime, 101))

    FROM tblDailyLog WHERE JobId = 1) = CONVERT(VARCHAR, GETDATE(), 101)

    -- Above determines Daily has been processed today

    BEGIN

    SELECT 'Daily already processed today'

    RETURN(0)

    END

    ELSE

    BEGIN

    IF (SELECT MAX(DailyDoneId) -- Ensures we receive only 1 record back

    FROM tblDailySearchLog TS

    WHERE TS.JobId = 1 AND FoundYN = -1 AND DATEDIFF(n, SearchDateTime, GETDATE()) < 30) IS NULL

    -- Above WHERE clause is to determine whether DONE file found within last (n) minutes

    BEGIN

    SELECT 'DONE file not found'

    RETURN(0)

    END

    ELSE

    BEGIN

    INSERT INTO tblDailyLog (JobId, LastRunDateTime) VALUES (1, GETDATE())

    EXEC msdb..sp_start_job 'Daily Disp' -- Executes the job Daily Disp

    END

    END

    GO

    // CODE to perform pull from LINKED SERVER

    CREATE PROCEDURE spA_DISPDailyRaw_Sessions

    /*

    Created:10/22/2002

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

    Location:SERVER

    Purpose:* IMPORT on a daily basis DISP daily records

    * replaces old DTS package <Daily Sessions Import>

    */

    @JobId NUMERIC

    AS

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwDuplicateRaw_Sessions]') and

    OBJECTPROPERTY(id, N'IsView') = 1)

    drop view [dbo].[vwDuplicateRaw_Sessions]

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vwRaw_SessionsInSessions]') and

    OBJECTPROPERTY(id, N'IsView') = 1)

    drop view [dbo].[vwRaw_SessionsInSessions]

    TRUNCATE TABLE dbo.Raw_Sessions

    If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions_Temp')

    DROP INDEX Raw_Sessions.IX_RawSessions_Temp

    If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions')

    DROP INDEX Raw_Sessions.IX_RawSessions

    If Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions1')

    DROP INDEX Raw_Sessions.IX_RawSessions1

    INSERT INTO Raw_Sessions

    (Account, Userid, Service, Sub_Account, Origin_Country, Billing_Country, Resource, Resource_Qty,

    Session_Begin, Session_End, System_Id, Dest_Net_Flag, Dnis, Modem_Speed, Isdn_Seconds,

    Isdn_Channels, Record_Cut, Chassis_Id, Port_Number, Disconnect_Type, Nas_Port_Type, Input_Bytes,

    Output_Bytes, Input_Packets, Output_Packets, Nas_Ip, Client_Ip, Client_Port_Dnis, Caller_Id)

    SELECT

    UPPER(Account), Userid, Service, Sub_Account, Origin_Country, Billing_Country, Resource, Resource_Qty,

    LEFT(Session_Begin, 23) Session_Begin, LEFT(Session_End, 23) Session_End, System_Id,

    Dest_Net_Flag, Dnis, Modem_Speed, Resource_Qty * ISNULL(Isdn_Channels, 0), Isdn_Channels, Record_Cut,

    ISNULL(Chassis_Id, '00000000'), ISNULL(Port_Number, '000'), ISNULL(Disconnect_Type, '0'),

    ISNULL(Nas_Port_Type, '0'), ISNULL(Input_Bytes, '0'), ISNULL(Output_Bytes, '0'),

    ISNULL(Input_Packets, '0'), ISNULL(Output_Packets, '0'), ISNULL(Nas_Ip, '255.255.255.255'),

    ISNULL(Client_Ip, '255.255.255.255'), ISNULL(Client_Port_Dnis, '19999999999'), Caller_Id

    FROM txtSrvr...[P2CAny#txt]

    -- Create temporary index for deletion process performance increase

    If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions_Temp')

    CREATE INDEX [IX_RawSessions_Temp]

    ON [dbo].[Raw_Sessions]([Account], [Userid], [Resource], [Session_Begin],

    [Session_End], [System_Id], [Chassis_Id], [Port_Number]) ON [PRIMARY]

    If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions')

    CREATE INDEX [IX_RawSessions] ON [dbo].[Raw_Sessions]([Account]) ON [PRIMARY]

    If not Exists (SELECT [name] FROM sysindexes WHERE [name] = 'IX_RawSessions1')

    CREATE INDEX [IX_RawSessions1] ON [dbo].[Raw_Sessions]([Resource]) ON [PRIMARY]

    -- Removes any old data from table from prior run(s) if any

    DELETE FROM tblDailyDuplicateCounts WHERE JobId = @JobId AND CONVERT(VARCHAR, RunDateTime, 101) = CONVERT(VARCHAR,

    GETDATE(), 101)

    -- inserts basic information into count table for tracking purposes

    INSERT INTO tblDailyDuplicateCounts (JobId, RunDateTime, RecordsLoadedIntoRawSessions)

    SELECT @JobId, GETDATE(), COUNT(*)

    FROM Raw_Sessions

    EXEC dbo.spCreateRawSessionViews

    GO

    // CODE for VB6 executable

    // normal module

    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

    // CODE for VB6 executable

    // 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



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • Andy,

    THANK YOU, THANK YOU, THANK YOU!!!!!

    In the bowels of your code, I finally found what I've not been able to find anywhere else... a replacement for using xp_CmdShell (security risk) to run a DIR command. 

    I knew that xp_DirTree would create a list of sub-directories off a given path but I didn't know it would produce a level controled list of files, as well (although I thought it should because it also uses XPStar.dll) which is why I kept looking).  Even the SQL "Black Book" didn't have the 'level' and 'filesflag' switches that you incorporated into your code.

    So even though your article is over 4 years old, I just wanted to say "Thanks"... you've made my job a heck of a lot easier ("Look Ma!  No DTS!!!  YIPPEEE!!!")

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 14 posts - 1 through 13 (of 13 total)

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