VB .NET Not Recognizing DTS Error

[andrea96]

I have a VB .NET program that runs a SQL Server 2000 DTS package. When I execute the DTS package in Enterprise Manager, I get this error and the package stops executing.

The task reported failure on execution.
The statement has been terminated.
Cannot insert duplicate key row in object 'STANDARD_CODES' with unique index 'F055_K01'.

However, when I execute the DTS package from VB .NET, it never throws an exception. When I debug, I can see that the program just stops executing in this sub.

    Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
        ByRef pbCancel As Boolean) _
        Implements DTS.PackageEvents.OnQueryCancel
        If EventSource.Length > 0 Then
            Console.WriteLine(" OnQueryCancel in {0}; pbCancel = {1}", _
                EventSource, pbCancel.ToString)
        Else
            Console.WriteLine(" OnQueryCancel; pbCancel = {0}", _
                pbCancel.ToString)
        End If
        pbCancel = False
    End Sub

Has anyone run into this problem before?

 

[TerraSamba]

Take a look at this:

http://support.microsoft.com/?kbid=221193

 

[andrea96]

I edited the DTS package and in Logging, selected Fail package on first error. This seems to have resolved the problem.

 

[LNBruno]

Andrea -

I'm having the reverse problem: the DTS pkg executes just fine on ES (and on automation), but fails through a VB.Net WebService.

Could you post your code?

< M!ke >

 

[andrea96]

This is the code I am running.

    Private Sub ExecuteDTSPackage(ByVal strPackageName As String)

        Dim UserTblDTS As DTS.Package
        Dim strServer As String
        Dim strUser As String
        Dim strPassword As String
        Dim cpContainer As UCOMIConnectionPointContainer
        Dim cpPoint As UCOMIConnectionPoint
        Dim PES As PackageEventsSink = New PackageEventsSink
        Dim guid As Guid = New Guid("10020605-EB1C-11CF-AE6E-00AA004A34D5")
        Dim intCookie As Integer

        Try
            UserTblDTS = New DTS.Package

            strServer = sGetINI(strINI, "UserTableLoad", "Server", " ")
            strUser = sGetINI(strINI, "UserTableLoad", "User", " ")
            strPassword = sGetINI(strINI, "UserTableLoad", "Pwd", " ")

            'Begin - set up events sink
            cpContainer = CType(UserTblDTS, UCOMIConnectionPointContainer)
            cpContainer.FindConnectionPoint(guid, cpPoint)
            cpPoint.Advise(PES, intCookie)
            'End - set up events sink

            UserTblDTS.LoadFromSQLServer(strServer, , , _
            DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, _ 
                , , , strPackageName, )

            UserTblDTS.Execute()
            UserTblDTS.UnInitialize()

            'Clean up
            UserTblDTS = Nothing
            cpPoint.Unadvise(intCookie)
            cpPoint = Nothing
            cpContainer = Nothing
            PES = Nothing

        Catch ex As System.Runtime.InteropServices.COMException
            ErrorFile(ex.Message)
            Environment.Exit(8)
            End
        Catch ex As Exception
            ErrorFile(ex.Message)
            Environment.Exit(8)
            End
        End Try
    End Sub

This is the class code, which is after the end of the module.

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
        ErrorFile("OnError in " & EventSource & "; ErrorCode = " & _
        ErrorCode & ", Source = " & Source & ", " & "Description = " & _
        description)
        Environment.Exit(8)

    End Sub

    Overridable Overloads Sub OnFinish(ByVal EventSource As String) _
        Implements DTS.PackageEvents.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

    End Sub

    Overridable Overloads Sub OnQueryCancel(ByVal EventSource As String, _
        ByRef pbCancel As Boolean) _
        Implements DTS.PackageEvents.OnQueryCancel
        pbCancel = False
    End Sub

    Overridable Overloads Sub OnStart(ByVal EventSource As String) _
        Implements DTS.PackageEvents.OnStart
    End Sub

End Class

I have found the info at [URL unfurl="true"]http://support.microsoft.com/?kbid=321525[/url] helpful.

Andrea