CODEWITHSUNDEEP
vb.nethttpssisetlscript-task
Alex McQueen
Alex McQueen

Reputation: 31

SSIS Script Task supress onerror

I have a script task which downloads a file using a HTTP connection object. This script task is part of a package which is called by another package. Sometimes the connection cannot be established. In these instances I want to retry the connection a number of times before finally raising an error if the connection attempts fail.

I tried to implement this. It appeared to work and the task does not fail. However an OnError event is still propagated every time an exception happens in the script task even though the script task doesn't fail. The fail occurs once control is passed from the child package back to the parent package.

Public Sub Main()
Dim tryTimes As Integer = 0 
Dim maxTimes As Integer = 4 
While (True) 
    Try
        Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
            'Create a new HTTP client connection
        Dim connection As New HttpClientConnection(nativeObject)
        Dim filename As String = Dts.Variables("Working_File").Value
        connection.DownloadFile(filename, True)
        Dts.TaskResult = ScriptResults.Success
        Exit While
    Catch ex As Exception
        If (tryTimes < maxTimes) Then
            tryTimes = tryTimes + 1
            Thread.Sleep(30000) 
        Else 
            MsgBox(ex.Message)
            Dts.TaskResult = ScriptResults.Failure
            Throw
        End If
    End Try
End While
End Sub

I am hoping to get a solution where the OnError event is not called unless the connection attempts fails a certain number of times.

Upvotes: 3

Views: 380

Answers (2)

Tim Mylott
Tim Mylott

Reputation: 2776

You'll want to use a label, outside the try, and a GoTo within your catch

    Public Sub Main()
        Dim tryTimes As Integer = 0
        Dim maxTimes As Integer = 4

RunCode: 'Label here
        While (True)

            Try
                'your code here

                Exit While
            Catch ex As Exception
                If (tryTimes < maxTimes) Then
                    tryTimes = tryTimes + 1
                    Thread.Sleep(30000)
                    GoTo RunCode 'after we catch the exception and eval tryTimes go back and retry
                Else

                    'MsgBox(ex.Message)
                    Dts.Events.FireError(-1, "", "Error message: " & ex.ToString(), "", 0)
                    Dts.TaskResult = ScriptResults.Failure
                    'Throw
                End If
            End Try
        End While
    End Sub

Upvotes: 1

Hadi
Hadi

Reputation: 37368

Try writing the same code an Fire a Warning on first 4 trial and on the 5th trial fire an error, i am not sure if it will works:

Public Sub Main()
    Dim tryTimes As Integer = 0 
    Dim maxTimes As Integer = 4 
    While (True) 
        Try
            Dim nativeObject As Object = Dts.Connections("HTTP Connection Manager").AcquireConnection(Nothing)
                'Create a new HTTP client connection
            Dim connection As New HttpClientConnection(nativeObject)
            Dim filename As String = Dts.Variables("Working_File").Value
            connection.DownloadFile(filename, True)
            Dts.TaskResult = ScriptResults.Success
            Exit While
        Catch ex As Exception
            If (tryTimes < maxTimes) Then
                tryTimes = tryTimes + 1
                Dts.Events.FireWarning(0, "Error ignored", _
                        "Retrying in 30 seconds", String.Empty, 0)
                Thread.Sleep(30000) 
            Else 
                Dts.Events.FireError(-1, "", "Error message: " & ex2.ToString(), "", 0)
                Dts.TaskResult = ScriptResults.Failure

            End If
        End Try
    End While
End Sub

Reference

Upvotes: 1

Related Questions