Solved

How do I use the  bcp utility to load text file from MSAccess module

Posted on 2007-03-20
16
507 Views
Last Modified: 2013-11-27
I am importing a text file from my local machine to a SQL Server database on a central network.  The data is being imported from MSAccess using the Transfer Text utility.  The table that is being loaded is LINKED to the MSAccess application.  The import is sooooooo slow.  If the table was a local MSAccess table, it's very fast, but the database portion of the application has recently been migrated to SQL Server.  I've tried importing the data into a local table and then APPENDING it to the LINKED table, but that's not any faster.  Question:  Can I use the bcp utility to do the load?  Would it be faster?  Can I write a VB/VBA module to execute the bcp utility?  If so, what is the syntax?   The current syntax I have in my VB/VBA module for the IMPORT using Transfer Text is this:

    strImportFile = "\\contach-share1\zuserstatistics_" & strMandt & ".txt"
    DoCmd.TransferText acImportDelim, "Zuserstatistics Import Spec", "tblUpldzuserstatistics", strImportFile, False, ""
0
Comment
Question by:CaroleTSullivan
  • 9
  • 7
16 Comments
 
LVL 7

Expert Comment

by:JTennessen
ID: 18759819
Yes, I believe you can find a solution that will speed this up. bcp isn't the only option SQL Server provides. Can you give me a few more details about this first. For example, what is the schema represented by Zuserstatistics Import Spec? How many rows, on average, are in the file being imported?

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18760058
JTennessen:
Here's the def of the Import Spec and there can be over 200,000 records to import.  Keep in mind it has to currently be fired from a module from MSAccess.  Thank you so much for your help!
Field1 Text
Field2 Text
Transcode Text
Jobname Text
Trans Text
Transuser Text
Transdate Text
Client Text
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18760931
OK, here's one option: you could use a Visual Basic DTS script directly in Access. First, here is the code, generated by SQL Server's DTS utility and tweaked by me:


  Option Compare Database
  Option Explicit
 
  '****************************************************************
  'Microsoft SQL Server 2000
  'Visual Basic file generated for DTS Package; modified by Jeff Tennessen
  'File Name:
  'Package Name:
  'Package Description:
  'Generated Date: 3/20/2007
  'Generated Time:
  '****************************************************************
 
  Public goPackageOld As New DTS.Package
  Public goPackage As DTS.Package2
 
  Public Sub Main(ByVal pstrFile As String, _
                  ByVal pstrServer As String, _
                  ByVal pstrDB As String, _
                  ByVal pstrOwner As String, _
                  ByVal pstrTable As String)
 
    Dim strDest As String
   
    strDest = "[" & pstrServer & "].[" & pstrOwner & "].[" & pstrTable & "]"
   
    Set goPackage = goPackageOld
 
    goPackage.Name = "Import File"
    goPackage.Description = ""
    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
   
    Dim oConnProperty As DTS.OleDBProperty
   
    '---------------------------------------------------------------------------
    ' 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("DTSFlatFile")
   
    oConnection.ConnectionProperties("Data Source") = pstrFile
    oConnection.ConnectionProperties("Mode") = 1
    oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
    oConnection.ConnectionProperties("File Format") = 1
    oConnection.ConnectionProperties("Column Delimiter") = ","
    oConnection.ConnectionProperties("File Type") = 1
    oConnection.ConnectionProperties("Skip Rows") = 0
    oConnection.ConnectionProperties("First Row Column Name") = False
    oConnection.ConnectionProperties("Max characters per delimited column") = 8000
   
    oConnection.Name = "Connection 1"
    oConnection.id = 1
    oConnection.Reusable = True
    oConnection.ConnectImmediate = False
    oConnection.DataSource = pstrFile
    oConnection.ConnectionTimeout = 60
    oConnection.UseTrustedConnection = False
    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
   
    '------------- a new connection defined below.
    'For security purposes, the password is never scripted
   
    Set oConnection = goPackage.Connections.New("SQLOLEDB")
   
    oConnection.ConnectionProperties("Integrated Security") = "SSPI"
    oConnection.ConnectionProperties("Persist Security Info") = True
    oConnection.ConnectionProperties("Initial Catalog") = pstrDB
    oConnection.ConnectionProperties("Data Source") = pstrServer
    oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"
   
    oConnection.Name = "Connection 2"
    oConnection.id = 2
    oConnection.Reusable = True
    oConnection.ConnectImmediate = False
    oConnection.DataSource = pstrServer
    oConnection.ConnectionTimeout = 60
    oConnection.Catalog = pstrDB
    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 = "Copy Data from " & pstrFile & " to " & strDest & " Step"
    oStep.Description = "Copy Data from " & pstrFile & " to " & strDest & " Step"
    oStep.ExecutionStatus = 1
    oStep.TaskName = "Copy Data from " & pstrFile & " to " & strDest & " Task"
    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
   
    '---------------------------------------------------------------------------
    ' create package tasks information
    '---------------------------------------------------------------------------
   
    '------------- call Task_Sub1 for task Copy Data from the input file to the destination table Task (Copy Data from the input file to the destination table Task)
    Call Task_Sub1(goPackage, pstrFile, strDest)
   
    '---------------------------------------------------------------------------
    ' Save or execute package
    '---------------------------------------------------------------------------
   
    'goPackage.SaveToSQLServer "(local)", "sa", ""
    goPackage.Execute
    tracePackageError goPackage
    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
 
  '-----------------------------------------------------------------------------
  ' error reporting using step.GetExecutionErrorInfo after execution
  '-----------------------------------------------------------------------------
  Public Sub tracePackageError(oPackage As DTS.Package)
 
    Dim ErrorCode As Long
    Dim ErrorSource As String
    Dim ErrorDescription As String
    Dim ErrorHelpFile As String
    Dim ErrorHelpContext As Long
    Dim ErrorIDofInterfaceWithError As String
    Dim i As Integer
   
    For i = 1 To oPackage.Steps.Count
      If oPackage.Steps(i).ExecutionResult = DTSStepExecResult_Failure Then
        oPackage.Steps(i).GetExecutionErrorInfo ErrorCode, ErrorSource, ErrorDescription, _
            ErrorHelpFile, ErrorHelpContext, ErrorIDofInterfaceWithError
        MsgBox oPackage.Steps(i).Name & " failed" & vbCrLf & ErrorSource & vbCrLf & ErrorDescription
      End If
    Next i
 
  End Sub
 
  '------------- define Task_Sub1 for task Copy Data from the input file to the destination table Task (Copy Data from the input file to the destination table Task)
  Public Sub Task_Sub1(ByVal goPackage As Object, _
                       ByVal pstrFile As String, _
                       ByVal pstrDest As String)
 
    Dim oTask As DTS.Task
    Dim oLookup As DTS.Lookup
   
    Dim oCustomTask1 As DTS.DataPumpTask2
    Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
    oTask.Name = "Copy Data from " & pstrFile & " to " & pstrDest & " Task"
    Set oCustomTask1 = oTask.CustomTask
   
    oCustomTask1.Name = "Copy Data from " & pstrFile & " to " & pstrDest & " Task"
    oCustomTask1.Description = "Copy Data from " & pstrFile & " to " & pstrDest & " Task"
    oCustomTask1.SourceConnectionID = 1
    oCustomTask1.SourceObjectName = pstrFile
    oCustomTask1.DestinationConnectionID = 2
    oCustomTask1.DestinationObjectName = pstrDest
    oCustomTask1.ProgressRowCount = 1000
    oCustomTask1.MaximumErrorCount = 0
    oCustomTask1.FetchBufferSize = 1
    oCustomTask1.UseFastLoad = True
    oCustomTask1.InsertCommitSize = 0
    oCustomTask1.ExceptionFileColumnDelimiter = "|"
    oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
    oCustomTask1.AllowIdentityInserts = False
    oCustomTask1.FirstRow = 0
    oCustomTask1.LastRow = 0
    oCustomTask1.FastLoadOptions = 2
    oCustomTask1.ExceptionFileOptions = 1
    oCustomTask1.DataPumpOptions = 0
   
    Call oCustomTask1_Trans_Sub1(oCustomTask1)
   
    goPackage.Tasks.Add oTask
    Set oCustomTask1 = Nothing
    Set oTask = Nothing
 
  End Sub
 
  Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)
 
    Dim oTransformation As DTS.Transformation2
    Dim oTransProps As DTS.Properties
    Dim oColumn As DTS.Column
   
    Set oTransformation = oCustomTask1.Transformations.New("DTS.DataPumpTransformCopy")
   
    oTransformation.Name = "DirectCopyXform"
    oTransformation.TransformFlags = 63
    oTransformation.ForceSourceBlobsBuffered = 0
    oTransformation.ForceBlobsInMemory = False
    oTransformation.InMemoryBlobSize = 1048576
    oTransformation.TransformPhases = 4
   
    Set oColumn = oTransformation.SourceColumns.New("Col001", 1)
   
    oColumn.Name = "Col001"
    oColumn.Ordinal = 1
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col002", 2)
   
    oColumn.Name = "Col002"
    oColumn.Ordinal = 2
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
     
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col003", 3)
   
    oColumn.Name = "Col003"
    oColumn.Ordinal = 3
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col004", 4)
   
    oColumn.Name = "Col004"
    oColumn.Ordinal = 4
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col005", 5)
   
    oColumn.Name = "Col005"
    oColumn.Ordinal = 5
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col006", 6)
   
    oColumn.Name = "Col006"
    oColumn.Ordinal = 6
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col007", 7)
   
    oColumn.Name = "Col007"
    oColumn.Ordinal = 7
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.SourceColumns.New("Col008", 8)
   
    oColumn.Name = "Col008"
    oColumn.Ordinal = 8
    oColumn.Flags = 32
    oColumn.Size = 8000
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.SourceColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Field1", 2)
   
    oColumn.Name = "Field1"
    oColumn.Ordinal = 2
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Field2", 3)
   
    oColumn.Name = "Field2"
    oColumn.Ordinal = 3
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Transcode", 4)
   
    oColumn.Name = "Transcode"
    oColumn.Ordinal = 4
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Jobname", 5)
   
    oColumn.Name = "Jobname"
    oColumn.Ordinal = 5
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Trans", 6)
   
    oColumn.Name = "Trans"
    oColumn.Ordinal = 6
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Transuser", 7)
   
    oColumn.Name = "Transuser"
    oColumn.Ordinal = 7
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Transdate", 8)
   
    oColumn.Name = "Transdate"
    oColumn.Ordinal = 8
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oColumn = oTransformation.DestinationColumns.New("Client", 9)
   
    oColumn.Name = "Client"
    oColumn.Ordinal = 9
    oColumn.Flags = 104
    oColumn.Size = 50
    oColumn.DataType = 129
    oColumn.Precision = 0
    oColumn.NumericScale = 0
    oColumn.Nullable = True
   
    oTransformation.DestinationColumns.Add oColumn
    Set oColumn = Nothing
   
    Set oTransProps = oTransformation.TransformServerProperties
   
    Set oTransProps = Nothing
   
    oCustomTask1.Transformations.Add oTransformation
    Set oTransformation = Nothing
 
  End Sub


I wasn't sure of some of the import spec properties, so I assumed that it was comma delimited, without text qualifiers, etc. If this is incorrect, the code is easily tweaked to account for the differences. Let me know if you need help with that.

To use it, just copy the code to a module in your Access database, then set a reference to the Microsoft DTSPackage Object Library. It should then compile. To run it, just execute the Main subroutine, passing in:
   the path and name of the file from which you'll be importing
   the name of the server to which you'll be importing
   the name of the database to which you'll be importing
   the name of the owner of the table to which you'll be importing (usually, this is "dbo")
   the name of the table to which you'll be importing

Give it a try and see how it works. I'm not sure what your performance expectations are, based on the previous method of using TransferText directly into Access. If this still doesn't perform well enough for you, let me know and we'll try another approach. I wish I could say that a given approach is always the fastest in all situations, but like so many things in life, it depends too much on the particulars to do much generalizing. We'll keep working at it until we get it where you need it to be or exhaust all the possibilities of which I'm aware.

Jeff
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18760945
Whoops. Just noticed one mistake in the code. The line:

   strDest = "[" & pstrServer & "].[" & pstrOwner & "].[" & pstrTable & "]"

should actually be:

   strDest = "[" & pstrDB & "].[" & pstrOwner & "].[" & pstrTable & "]"

Sorry about that!

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18761182
JTennessen:  Wow!  That's quite a bit of work you did.  I have some bad news....I have only about 50 files to import and I'd hate to have to write that much code for all of them, but I will try your suggestion for atleast the huge file that was taking so long to import to see if it helps with performance for that particular file.  I was really hoping that I could just execute a simple command line that would call the bcp utility.  But now you've gotten me curious and I will try your code above tomorrow when I get back to the office.  Thank you so much.  I'll let you know how it goes!
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18761500
That's OK. This is code I use all the time, and I just modified it a little to fit your schema. I really think this could work, though. You can just call the Main() subroutine and pass in the filename, and the other parameters. Something like this:

    strImportFile = "\\contach-share1\zuserstatistics_" & strMandt & ".txt"
    Main strImportFile, "ServerName", "DBName", "OwnerName", "tblUpldzuserstatistics"

That's essentially the same amount of code you used to use for the TransferText solution. If you still don't think this is the right approach, let me know and I'll give you a bcp-based one.

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18764324
JTennessen:  Re:  DTSPackage Object Library       Can you tell me exactly what dll this is?  It is not showing up in my reference window...thank you.
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18764391
Sure. It would be in your Program Files directory, under \Microsoft SQL Server\80\Tools\Binn\dtspkg.dll.

Jeff
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:CaroleTSullivan
ID: 18764491
JTennessen:  I need to change the routine to also pass a logon userid and password, but I couldn't figure out where to put it.  Also, the delimiter on the input file is a "|".  I think I know where to change that, but I'm not a 100% sure.  Can you advise?  Thank you in advance for being so patient with me!
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18765021
No problem at all. Before giving you the modified code, I have to admit that we use Windows Authentication with all of our SQL Server installations, so I have no practical way to test whether the SQL auth approach below actually works. If it doesn't, maybe another expert can advise us? Anyway, here is the Main() subroutine, modified to use SQL auth and a pipe character as the delimiter:

    Public Sub Main(ByVal pstrFile As String, _
                    ByVal pstrServer As String, _
                    ByVal pstrDB As String, _
                    ByVal pstrOwner As String, _
                    ByVal pstrTable As String, _
                    ByVal pstrUserID As String, _
                    ByVal pstrPassword As String)
   
      Dim strDest As String
     
      strDest = "[" & pstrDB & "].[" & pstrOwner & "].[" & pstrTable & "]"
     
      Set goPackage = goPackageOld
   
      goPackage.Name = "Import File"
      goPackage.Description = ""
      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
     
      Dim oConnProperty As DTS.OleDBProperty
     
      '---------------------------------------------------------------------------
      ' 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("DTSFlatFile")
     
      oConnection.ConnectionProperties("Data Source") = pstrFile
      oConnection.ConnectionProperties("Mode") = 1
      oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
      oConnection.ConnectionProperties("File Format") = 1
      oConnection.ConnectionProperties("Column Delimiter") = "|"
      oConnection.ConnectionProperties("File Type") = 1
      oConnection.ConnectionProperties("Skip Rows") = 0
      oConnection.ConnectionProperties("First Row Column Name") = False
      oConnection.ConnectionProperties("Max characters per delimited column") = 8000
     
      oConnection.Name = "Connection 1"
      oConnection.id = 1
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = pstrFile
      oConnection.ConnectionTimeout = 60
      oConnection.UseTrustedConnection = False
      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
     
      '------------- a new connection defined below.
      'For security purposes, the password is never scripted
     
      Set oConnection = goPackage.Connections.New("SQLOLEDB")
     
      oConnection.ConnectionProperties("Persist Security Info") = True
      oConnection.ConnectionProperties("User ID") = pstrUserID
      oConnection.ConnectionProperties("Password") = pstrPassword
      oConnection.ConnectionProperties("Initial Catalog") = pstrDB
      oConnection.ConnectionProperties("Data Source") = pstrServer
      oConnection.ConnectionProperties("Application Name") = "DTS  Import/Export Wizard"
     
      oConnection.Name = "Connection 2"
      oConnection.id = 2
      oConnection.Reusable = True
      oConnection.ConnectImmediate = False
      oConnection.DataSource = pstrServer
      oConnection.UserID = pstrUserID
      oConnection.Password = pstrPassword
      oConnection.ConnectionTimeout = 60
      oConnection.Catalog = pstrDB
      oConnection.UseTrustedConnection = True
      oConnection.UseDSL = False
     
      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 = "Copy Data from " & pstrFile & " to " & strDest & " Step"
      oStep.Description = "Copy Data from " & pstrFile & " to " & strDest & " Step"
      oStep.ExecutionStatus = 1
      oStep.TaskName = "Copy Data from " & pstrFile & " to " & strDest & " Task"
      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
     
      '---------------------------------------------------------------------------
      ' create package tasks information
      '---------------------------------------------------------------------------
     
      '------------- call Task_Sub1 for task Copy Data from the input file to the destination table Task (Copy Data from the input file to the destination table Task)
      Call Task_Sub1(goPackage, pstrFile, strDest)
     
      '---------------------------------------------------------------------------
      ' Save or execute package
      '---------------------------------------------------------------------------
     
      'goPackage.SaveToSQLServer "(local)", pstrUserID, pstrPassword
      goPackage.Execute
      tracePackageError goPackage
      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

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18765262
Jeff:  thanks so much for trying, but I'm still getting the login error.  It is still trying to log on using windows authentication.  
0
 
LVL 7

Accepted Solution

by:
JTennessen earned 500 total points
ID: 18765822
Hmm. That's too bad. I'm afraid that, since I can't really test it with a SQL auth login, I'm not going to be much help in figuring out what's wrong. Maybe another expert here can lend a hand?

In case no one is able to get that working, let's go ahead and try another approach. This one uses the T-SQL BULK INSERT command. The first thing to do is to create a stored procedure to do the actual work:

    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO
    CREATE PROC usp_InsertUserStats
    (@File  AS nvarchar(255),
     @DB    AS sysname,
     @Owner AS sysname,
     @Table AS sysname)
    AS
          DECLARE @SQL AS nvarchar(4000)
          
          SET @SQL = N'BULK INSERT [' + @DB + '].[' + @Owner + '].[' + @Table + ']
                FROM ''' + @File + '''
                WITH (DATAFILETYPE = ''char'',
                      FIELDTERMINATOR = ''|'')'
          
          EXEC master..sp_executesql @SQL
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

Just copy that code into Query Analyzer in the DB in question and execute it. Once it's created, the next step is to create a procedure in Access to call it. This code would go in any module:

    Public Sub BulkInsert(ByVal pstrFile As String, _
                          ByVal pstrServer As String, _
                          ByVal pstrDB As String, _
                          ByVal pstrOwner As String, _
                          ByVal pstrTable As String, _
                          ByVal pstrUserID As String, _
                          ByVal pstrPassword As String)
   
      Dim cm As New ADODB.Command
     
      With cm
        .ActiveConnection = "Provider=SQLOLEDB.1;" & _
                            "Password=" & pstrPassword & ";" & _
                            "Persist Security Info=True;" & _
                            "User ID=" & pstrUserID & ";" & _
                            "Initial Catalog=" & pstrDB & ";" & _
                            "Data Source=" & pstrServer
        .CommandType = adCmdStoredProc
        .CommandText = "usp_InsertUserStats"
       
        .Parameters("@File").Value = pstrFile
        .Parameters("@DB").Value = pstrDB
        .Parameters("@Owner").Value = pstrOwner
        .Parameters("@Table").Value = pstrTable
       
        .Execute Options:=adExecuteNoRecords
      End With
     
      Set cm = Nothing
   
    End Sub

(Note that this assumes that a reference is set to the ADODB type library. This should be the case with Access 2003, but let me know if it isn't in your case, and I'll help you get that set.)

Then you would simply call the BulkInsert() subroutine very similarly to how you called Main() in the DTS-based solution:

    strImportFile = "\\contach-share1\zuserstatistics_" & strMandt & ".txt"
    BulkInsert strImportFile, "<servername>", "<dbname>", "<ownername>", "tblUpldzuserstatistics", "<username>", "<password>"

Just replace the values in angle brackets with the appropriate names from your DB schema. Give this a try and let me know whether it works for you.

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18766327
Jeff:  Ohhhhh, I'm getting closer.  I did everything you suggested and I'm getting an error that says I do not have permission to use the bulk load statement.  Is that on the database side and if so, do I need to contact the dba?  Or is that application side?  I get the error when executing the following statement...
 .Execute Options:=adExecuteNoRecords  

Thanks!
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18766554
Yes, it sounds like your login has not been given that permission. That is definitely on the database side. The easiest way to do it would be for your DBA to add you to the Bulk Insert Administrators server role. This is a built-in role in SQL Server 2000 that gives uses who belong to it permission to run BULK INSERT on databases to which they have access and to add other users to the Bulk Insert Administrators role. It does not confer any additional rights, so it is a relatively low-risk operation. Let me know whether that solves the problem.

Jeff
0
 

Author Comment

by:CaroleTSullivan
ID: 18767645
Jeff:
I'm going to close this question and award you all the points for the fantastic advice.  My dba did grant me BULK INSERT permission, but then I ran into another permissions problem.  UGHHHH!!  Since then, I have met with my users and they said it was alright to try and schedule an SSIS formerly known as DTS(Sql Server 2005) package to execute the import.  Don't know how to do that just yet, but I'm going to try it that way.  I already tried to just put together an import and ran into permission problems but I will have to work with our dba on that one.  Thank you again and have a great day!
0
 
LVL 7

Expert Comment

by:JTennessen
ID: 18769186
Well, thanks so much for the points. I am sorry that I didn't get you an end-to-end solution, but I'd be glad to continue to help in any way I can. Best of luck!

Jeff
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now