Loading a Series of Flat Files

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 Andy Jones » Loading a Series of Flat Files

14 posts, Page 2 of 2

««1 2

Loading a Series of Flat Files

Rate Topic

Display Mode

Topic Options

Author

Message

AJ Ahrens

Posted Tuesday, November 12, 2002 6:13 AM

SSCommitted

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

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

Post #45139

johnhind

Posted Tuesday, November 12, 2002 6:46 AM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, February 13, 2005 5:21 AM
Points: 3, Visits: 1

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

John

Post #45140

AJ Ahrens

Posted Tuesday, November 12, 2002 7:51 AM

SSCommitted

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

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

Post #45141

Jeff Moden

Posted Tuesday, May 09, 2006 1:18 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #278750

« Prev Topic | Next Topic »

14 posts, Page 2 of 2

««1 2

Permissions