SQLServerCentral / Discuss Content Posted by Andy Jones / Article Discussions / Article Discussions by Author / Loading a Series of Flat Files / Latest Posts

RE: Loading a Series of Flat Files

Jeff Moden — Tue, 09 May 2006 13:18:00 GMT

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!!!")

RE: Loading a Series of Flat Files

AJ Ahrens — Tue, 12 Nov 2002 07:51:00 GMT

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 SERVERexec sp_addlinkedserver @server = 'txtSrvr', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet (Text IISAM)', @datasrc = 'c:\MyDir\', @provstr = 'Text'goexec 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 ExplicitPublic goPackageOld As New DTS.PackagePublic goPackage As DTS.Package2Private 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 scriptedSet 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 oConnectionSet oConnection = Nothing'---------------------------------------------------------------------------' create package steps information'---------------------------------------------------------------------------Dim oStep As DTS.Step2Dim oPrecConstraint As DTS.PrecedenceConstraint'------------- a new step defined belowSet 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 oStepSet oStep = Nothing'------------- a new step defined belowSet 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 oStepSet oStep = Nothing'------------- a precedence constraint for steps defined belowSet 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 oPrecConstraintSet 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.ExecutegoPackage.Uninitialize'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package lineSet goPackage = NothingSet goPackageOld = NothingEnd 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.TaskDim oLookup As DTS.LookupDim oCustomTask1 As DTS.CreateProcessTask2Set 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 oTaskSet oCustomTask1 = NothingSet oTask = NothingEnd 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.TaskDim oLookup As DTS.LookupDim oCustomTask2 As DTS.ExecuteSQLTask2Set 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 oTaskSet oCustomTask2 = NothingSet oTask = NothingEnd Sub// CODE for stored-procedure that will fire off daily process if all requirements are TRUECREATE 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*/ASIF (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) ENDELSE 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 ENDGO// CODE to perform pull from LINKED SERVERCREATE 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 NUMERICASif 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_SessionsIf Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions_Temp') DROP INDEX Raw_Sessions.IX_RawSessions_TempIf Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions') DROP INDEX Raw_Sessions.IX_RawSessionsIf Exists (SELECT [name] FROM sysindexes WHERE [name]= 'IX_RawSessions1') DROP INDEX Raw_Sessions.IX_RawSessions1INSERT 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)SELECTUPPER(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_IdFROM txtSrvr...[P2CAny#txt]-- Create temporary index for deletion process performance increaseIf 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 anyDELETE FROM tblDailyDuplicateCounts WHERE JobId = @JobId AND CONVERT(VARCHAR, RunDateTime, 101) = CONVERT(VARCHAR, GETDATE(), 101)-- inserts basic information into count table for tracking purposesINSERT INTO tblDailyDuplicateCounts (JobId, RunDateTime, RecordsLoadedIntoRawSessions) SELECT @JobId, GETDATE(), COUNT(*) FROM Raw_SessionsEXEC dbo.spCreateRawSessionViewsGO// CODE for VB6 executable// normal moduleOption ExplicitGlobal bRanToday As BooleanGlobal sServerName As StringGlobal sConn As StringGlobal iJobId As IntegerSub 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 commentsDim sFile As StringDim adoConn As ADODB.ConnectionDim adoCmd As ADODB.CommandDim iFound As IntegerDim clsCheck As New clsCheckIfRanTodayDim sTest As StringDim sFilePath As StringOn 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 WithCleanUp: 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 SubErrorTrap: 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 performedEnd Sub// CODE for VB6 executable// Class modulePrivate Function fDailyAlreadyProcessedToday()Dim adoCon As ADODB.ConnectionDim adoCom As ADODB.CommandDim 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 IfCleaner: 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 connectionEnd FunctionPrivate Sub Class_Initialize() fDailyAlreadyProcessedToday '' Runs PRIVATE function aboveEnd Sub

RE: Loading a Series of Flat Files

johnhind — Tue, 12 Nov 2002 06:46:00 GMT

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

RE: Loading a Series of Flat Files

AJ Ahrens — Tue, 12 Nov 2002 06:13:00 GMT

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.

RE: Loading a Series of Flat Files

Scorpion_66 — Mon, 11 Nov 2002 15:28:00 GMT

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

RE: Loading a Series of Flat Files

Deuce — Mon, 11 Nov 2002 12:55:00 GMT

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

RE: Loading a Series of Flat Files

andyj93 — Mon, 11 Nov 2002 03:11:00 GMT

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,21,2,31etc...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

RE: Loading a Series of Flat Files

Deuce — Fri, 08 Nov 2002 17:03:00 GMT

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

RE: Loading a Series of Flat Files

philcart — Thu, 31 Oct 2002 16:08:00 GMT

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 ThanksPhill CarterEdited by - phillcart on 10/31/2002 4:11:12 PM

RE: Loading a Series of Flat Files

jloesch — Thu, 31 Oct 2002 15:10:00 GMT

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

RE: Loading a Series of Flat Files

andyj93 — Thu, 31 Oct 2002 08:40:00 GMT

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

RE: Loading a Series of Flat Files

johnhind — Thu, 31 Oct 2002 03:00:00 GMT

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 !thanksJohn, UK

RE: Loading a Series of Flat Files

philcart — Thu, 31 Oct 2002 02:36:00 GMT

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  endendelsebegin  flag errorend

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

Loading a Series of Flat Files

andyj93 — Wed, 30 Oct 2002 00:00:00 GMT

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