Hi,

I am trying to use DTS Package Object library to bulk insert a flat
file on my local machine into sql server. The bulk insert works fine
from query analyzer but in vb I get error: 80040e14 - Method Execute
failed.

Here is my bulk insert function:

Private Sub GenericTaskPackage()
Dim oConnection As DTS.Connection
Dim oStep As DTS.Step
Dim oTask As DTS.Task
Dim oCustomTask As DTS.BulkInsertTask 'TaskObject

On Error GoTo PackageError

Set oConnection = oPackage.Connections.New("SQLOLEDB")
Set oStep = oPackage.Steps.New
Set oTask = oPackage.Tasks.New("DTSBulkInsertTask")
Set oCustomTask = oTask.CustomTask

SetConnections oPackage, oConnection

With oStep
.Name = "GenericPkgStep"
.ExecuteInMainThread = True
End With

'Customize the Task Object
With oCustomTask
.Name = "GenericPkgTask"
.DataFile = App.Path & "\bvi_trv.txt"
.ConnectionID = 1
.DestinationTableName = "edihandoff_flight_bv"
.FieldTerminator = "|"
.RowTerminator = "\r\n"
End With

oStep.TaskName = oCustomTask.Name

With oPackage
.Steps.Add oStep
.Tasks.Add oTask
.FailOnError = True
End With

'Run the package and release references.
oPackage.Execute

Set oConnection = Nothing
Set oCustomTask = Nothing
Set oTask = Nothing
Set oStep = Nothing
oPackage.UnInitialize

Exit Sub

PackageError:
Dim sMsg As String
sMsg = "Package failed error: " & sErrorNumConv(Err.Number) & _
vbCrLf & Err.Description & vbCrLf & sAccumStepErrors(oPackage)
MsgBox sMsg, vbExclamation, oPackage.Name
End Sub

Can anyone shed some light?

Ally