Check for an empty csv file in HTTP location

Question

I am trying to follow Determine if file is empty (SSIS) to see if the file is empty at the HTTP location. I cant download it to begin with as the process is stuck at source and wont let my package finish. All I want to do is to query to source file, if its 0 records, exit the process and send email.

I have the email part working, but I am not sure how to check for the 0 records at source. I am using vb.net as my scripting language. Here's a snippet of what I have so far:

Public Sub Main()

        ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
        Dim nativeObject As Object = Dts.Connections("HTTP_FileName").AcquireConnection(Nothing)

        ' Create a new HTTP client connection
        Dim connection As New HttpClientConnection(nativeObject)



        ' Download the file #1
        ' Save the file from the connection manager to the local path specified
        Dim filename As String = "DestinationPath"
        connection.DownloadFile(filename, True)

        Dts.TaskResult = ScriptResults.Success
    End Sub

EDIT

If not 0 records, even a check for a 0kb file at the HTTP location should serve the purpose. It could technically check the filesize and then fail the script so as to raise the appropriate failure message.

Answer 1

if you are using Microsoft .Net Framework 4 you can use System.Net library to achieve that:

You can Use the Following Code:

Imports System.Net

Public Sub Main()

    ' Get the unmanaged connection object, from the connection manager called "HTTP Connection Manager"
    Dim nativeObject As Object = Dts.Connections("HTTP_FileName").AcquireConnection(Nothing)

    ' Create a new HTTP client connection
    Dim connection As New HttpClientConnection(nativeObject)

    Dim webStream As IO.Stream
    Dim req As HttpWebRequest
    Dim res As HttpWebResponse
'Assuming that Dts.Connections("HTTP_FileName").ConnectionString Contains the file URL

    req = WebRequest.Create(Dts.Connections("HTTP_FileName").ConnectionString)

    req.Method = "GET" ' Method of sending HTTP Request(GET/POST)

    res = req.GetResponse() ' Send Request

    webStream = res.GetResponseStream() ' Get Response

    Dim webStreamReader As New IO.StreamReader(webStream)

    If webStreamReader.ReadToEnd.Length = 0 Then
          msgbox("File is Empty")
    Else
          Dim filename As String = "DestinationPath"
          connection.DownloadFile(filename, True)              
    End If

    Dts.TaskResult = ScriptResults.Success
End Sub

Note: the way that @NoAlias provided is working Also

If connection.DownloadData().Length = 0 Then

 'Logic for when there is no data.

End If