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.

score 1 · Accepted Answer · answered Feb 16 '17 at 22:28

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

Wondering, if the file is 0Kb, can we trigger a task level failure? That would be perfect. — rvphx, Feb 16 '17 at 23:14

NoAlias · Answer 2 · 2017-02-16T16:04:26.023

0

Based on Edit of Question:

If it is just a matter of checking the length of the response, you can do this:

If connection.DownloadData().Length = 0 Then

     'Logic for when there is no data.

End If

You can get the raw string data and validate the file that way:

Dim strFileContents = System.Text.Encoding.UTF8.GetString(connection.DownloadData())

'Example file has header row but no content rows.  Your specific validation will be based 
'on what you're expecting from an empty csv file. 
If strFileContents Is Nothing OrElse  strFileContents.Split(System.Environment.NewLine).Count < 2 Then
     'Empty file.
End If

edited Feb 16 '17 at 16:04

answered Feb 13 '17 at 19:31

NoAlias

9,218
2
27
46

Thanks for that NoAlias. I tried to retrofit this code into the snipped I provided and it doesn't work. Sorry, not that great with vb.net or C#. Just plain SQL. – rvphx Feb 13 '17 at 19:40
Hmm, you may have to add some references. See if this helps: https://msdn.microsoft.com/en-us/library/ms136007.aspx – NoAlias Feb 15 '17 at 22:37
I also added a null check on strFileContents, although I don't think that will make a difference. It's worth a try though. – NoAlias Feb 15 '17 at 22:40
The problem is, the file processing is stuck at the upstream source and it is not signaling an end. The file does get downloaded at the end with a 0Kb size, but the script task does not finish. Not sure if this is a known behavior. I will keep this bounty open until we figure out a solution. – rvphx Feb 16 '17 at 21:51

Check for an empty csv file in HTTP location

2 Answers2