CODEWITHSUNDEEP
sql-servervb.nethttpssisvb.net-2010
rvphx
rvphx

Reputation: 2402

Check for an empty csv file in HTTP location

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.

Upvotes: 3

Views: 272

Answers (2)

Hadi
Hadi

Reputation: 37313

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

Upvotes: 1

NoAlias
NoAlias

Reputation: 9193

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

Upvotes: 0

Related Questions