Handle exceptions in VB.NET SSIS script with WebClient (FTP download)

Question

In SSIS I am using a VB.NET script task to download a file from an FTP folder.

The script is the following

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net

Public Class ScriptMain
    Public Sub Main()
        Dim objWebClient As WebClient = New WebClient()
        Dim strDownloadURL As String = "ftp://mydownloadhosting.com/myfolder/" + Dts.Variables("GetDate").Value.ToString() + "_daily.xml"
        Dim strFileName As String = Dts.Variables("WorkingFile").Value.ToString()
        Dim wp As WebProxy = New WebProxy("my.proxy.local", 1234)

        objWebClient.Proxy = wp
        objWebClient.Credentials = New System.Net.NetworkCredential("username", "password")
        objWebClient.DownloadFile(strDownloadURL, strFileName)

        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

it works correctly but my target is to manage the exception, in particular to discriminate between:

file not found
all other problems (timeout, problem with proxy, ...)

I have made some research about how to manage exception with WebClient() and I have found these:

which they give different forms of the following:

try
{
    // try to download file here
}
catch (WebException ex)
{
    if (ex.Status == WebExceptionStatus.ProtocolError)
    {
        if (((HttpWebResponse)ex.Response).StatusCode == HttpStatusCode.NotFound)
        {
            // handle the 404 here
        }
    }
    else if (ex.Status == WebExceptionStatus.NameResolutionFailure)
    {
        // handle name resolution failure
    }
}

The main problem is that my code is in VB.NET and all the posted answered are written in C#, how can make a try/catch construct to handle an exception in my code?

Martin Prikryl · Answer 1 · 2019-03-20T11:03:28.047

An equivalent code in VB.NET is:

Try
    ' try to download file here
Catch ex As WebException
    If ex.Status = WebExceptionStatus.ProtocolError Then
        If DirectCast(ex.Response, HttpWebResponse).StatusCode = HttpStatusCode.NotFound Then
            ' // handle the 404 here
        End If
    ElseIf ex.Status = WebExceptionStatus.NameResolutionFailure Then
        ' handle name resolution failure
    End If
End Try

Though the above/your code is for HTTP, not for FTP. FTP has different status codes.

For FTP, use:

FtpWebResponse and
FtpStatusCode.

For some FTP examples, see:

Hadi · Accepted Answer · 2019-03-20T10:06:26.883

0

There are many C# to VB.NET converters that you can refer to when you need to convert simple codes:

The equivalent VB.NET code is:

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net

Public Class ScriptMain
    Public Sub Main()

        Try

            Dim objWebClient As WebClient = New WebClient()
            Dim strDownloadURL As String = "ftp://mydownloadhosting.com/myfolder/" + Dts.Variables("GetDate").Value.ToString() + "_daily.xml"
            Dim strFileName As String = Dts.Variables("WorkingFile").Value.ToString()
            Dim wp As WebProxy = New WebProxy("my.proxy.local", 1234)

            objWebClient.Proxy = wp
            objWebClient.Credentials = New System.Net.NetworkCredential("username", "password")
            objWebClient.DownloadFile(strDownloadURL, strFileName)

            Dts.TaskResult = Dts.Results.Success

        Catch ex As WebException

            If ex.Status = WebExceptionStatus.ProtocolError Then

                If (CType(ex.Response, HttpWebResponse)).StatusCode = HttpStatusCode.NotFound Then

                   'handle the 404 here

                End If
            ElseIf ex.Status = WebExceptionStatus.NameResolutionFailure Then

                'handle name resolution failure

            End If

            Dts.TaskResult = Dts.Results.Failure

        End Try


    End Sub
End Class

edited Mar 20 '19 at 10:06

answered Mar 20 '19 at 09:45

Hadi

36,233
13
65
124

The way you have combined the code with `ftp://` and `HttpWebResponse` will never work. The cast of `ex.Response` to `HttpWebResponse` will fail. – Martin Prikryl Mar 20 '19 at 09:50
@MartinPrikryl i didn't check to code since the OP mentioned that the code is working correctly. But why casting will fail? – Hadi Mar 20 '19 at 09:53
Because for FTP, the response is of type `FtpWebResponse`. See my answer. -- I do not think that OP ever tested the C# code. OP just incorrectly assumes that the code is correct. – Martin Prikryl Mar 20 '19 at 09:54
@MartinPrikryl oops i didn't mention that. You're right , as i mentioned before i only tried to provide the full equivalent VB.NET code without checking the functionality. – Hadi Mar 20 '19 at 09:56
@MartinPrikryl i agree with you. I will add these information to your answer. – Hadi Mar 20 '19 at 10:06
1

@MartinPrikryl since the goal is helping the OP i also upvoted yours. Thank you – Hadi Mar 20 '19 at 10:07

Handle exceptions in VB.NET SSIS script with WebClient (FTP download)

2 Answers2