I have a DTS package that is used to import a flat file into my database.
This file is a rotating log file that could hold information that has been
imported previously so I was testing running the import multiple times when
I ran into this COMExcpetion (I'm calling the DTS package from a C#
application that I wrote when running from SQL Server directly there is no
error). In doing some research I found out that the problem occurs with 1
type of record in my file when there are more that 1 row of that type and
I'm re-importing data that already exists. My file has 5 different types
of records in it and the other 4 types don't have this problem.

I'm wondering what I can/should do to try and track down this error. Any
help or ideas are greatly appreciated.

My DTS package imports my CSV file into a temporary table then runs SQL
statements on that table to move the various types of records into their
specific tables.

--
Message posted via http://www.sqlmonster.com

Sorry I should have added this is the specific error I'm getting:
COMException -2147220441
Execution was canceled by user.
at DTS.Package2Class.Execute()
at Import.ExecPkgWithEvents.ExecutePackageOnFile(Stri ng packageName,
String fileName) in c:\documents and settings\rbooth\iws\
activityreportingsystem\import\
execpkgwithevents.cs:line 65

And the code that is generating it is:
package.LoadFromSQLServer(
serverName,
userName,
password,
DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedC onnection,
null,
null,
null,
packageName,
ref pVarPersistStgOfHost);

package.Execute(); // Line 65
package.UnInitialize();
package = null;
CnnctPt.Unadvise(iCookie);

--
Message posted via http://www.sqlmonster.com

Just FYI for others
this is a bug in VB
BUG: DTS Package Execution Is Canceled Unexpectedly in a Visual Basic
Application
http://support.microsoft.com/default...b;en-us;319058


"Rob Booth via SQLMonster.com" wrote:

The Bug article refers to VB6;

Here is the .net solution:

Public Class Form1
Inherits System.Windows.Forms.Form
Dim WithEvents pkg As DTS.Package
Private oSQLServerDMOApp As SQLDMO.Application
Private cSQLconnect As SQLDMO.SQLServer
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim stp As DTS.Step
pkg = New DTS.Package

'Begin - set up events sink
Dim cpContainer As UCOMIConnectionPointContainer
cpContainer = CType(pkg, UCOMIConnectionPointContainer)
Dim cpPoint As UCOMIConnectionPoint
Dim PES As PackageEventsSink = New PackageEventsSink
Dim guid As Guid = New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
cpContainer.FindConnectionPoint(guid, cpPoint)
Dim intCookie As Integer
cpPoint.Advise(PES, intCookie)
' End

'load DTS Job
pkg.LoadFromSQLServer(ServerName:="developer7\ethe rrien", _

Flags:=DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_ UseTrustedConnection, _
PackageName:="Sample Test Package - Net Send")

For Each stp In pkg.Steps
stp.ExecuteInMainThread = True
Next

pkg.FailOnError = True
pkg.Execute()

'variable added to pass into DTS object for return error resultants
Dim lErr As Integer
Dim sSource, sDesc As String

For Each stp In pkg.Steps
If stp.ExecutionResult =
DTS.DTSStepExecResult.DTSStepExecResult_Failure Then
stp.GetExecutionErrorInfo(lErr, sSource, sDesc)
Console.WriteLine("Step " & stp.Name & _
"/" & stp.Description & " Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf)
Else
Console.WriteLine("Step " & stp.Name & _
"/" & stp.Description & " Succeeded" & vbCrLf & vbCrLf)
End If
Next

pkg.UnInitialize()
pkg = Nothing
cpPoint.Unadvise(intCookie)
cpPoint = Nothing
cpContainer = Nothing
PES = Nothing
End Sub

Public Class PackageEventsSink
Implements DTS.PackageEvents
Overridable Overloads Sub OnError(ByVal EventSource As String, _
ByVal ErrorCode As Integer, ByVal Source As String, _
ByVal Description As String, ByVal HelpFile As String, _
ByVal HelpContext As Integer, ByVal IDofInterfaceWithError
As String, _
ByRef pbCancel As Boolean) Implements
DTS.PackageEvents.OnError
Console.WriteLine("An error occurred." & vbCrLf & _
"Event source: " & EventSource & vbCrLf & _
"Error code: " & ErrorCode & vbCrLf & _
"Source: " & Source & vbCrLf & _
"Description: " & Description)
End Sub
Overridable Overloads Sub OnFinish(ByVal EventSource As String) _
Implements DTS.PackageEvents.OnFinish
Console.WriteLine(" OnFinish")
End Sub
Overridable Overloads Sub OnProgress(ByVal EventSource As String, _
ByVal ProgressDescription As String, ByVal PercentComplete
As Integer, _
ByVal ProgressCountLow As Integer, ByVal ProgressCountHigh
As Integer) _
Implements DTS.PackageEvents.OnProgress
Static cnt As Integer
Console.WriteLine("Step " & cnt)
cnt = cnt + 1

End Sub
Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
ByRef pbCancel As Boolean) Implements
DTS.PackageEvents.OnQueryCancel
Static cnt As Integer
If pbCancel Then
cnt = cnt + 1
Console.WriteLine("Resetting value of pbCancel flag, count =
" & cnt)
pbCancel = False
End If
End Sub
Overridable Overloads Sub OnStart(ByVal EventSource As String) _
Implements DTS.PackageEvents.OnStart
Console.WriteLine(" OnStart")
End Sub
End Class

End Class