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