greenmtnsun

HTTP Authenticatoin Download of File in SSIS to CSV file

I recently needed to perform a download of a CSV file with HTTP Authentication in SSIS to a CSV file.  Its possible, but I needed a script to do it.  I couldn't use an HTTP connection because nothing in SSIS would consume it.   Nothing, including the File System, WMI and execute process tasks would work in the control flow and none of the data flow sources would consume the file.  I was forced to use a script since the company I was downloading from had no FTP and no web service for this file. 

1. Create a script task on the control flow tab.
2. Populate your URL in a prior step to a variable.  I used a SQL Task to format a complicated file name into the URL.
   
3. Make sure your script task passes the URL in the ReadOnlyVariables to the script.
   
4. Use this script below.  Correct the place for your UNC path for your output file, user id, password.
5. Consume your data in a data flow task.
   

I hope this helps people; it took me a while to figure it out. 

Keith

p.s. Thanks to my co-worker Kirk Palmer for assistance with the VB.NET script.
 

I used this VB.NET Script below:

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net
Imports System.IO
Imports System.Collections.Generic
Imports System.Text

Public Class ScriptMain
    Public Sub Main()
        Try
            Dim restURL As New StringBuilder()
            Dim restRequest As HttpWebRequest
            Dim restResponse As HttpWebResponse
            Dim tDoc As StreamReader
            ' modify the date, report name, merchant ID, and processing environment (production/test) as needed
            restURL.AppendFormat(Dts.Variables("URL").Value.ToString())
            restRequest = DirectCast(WebRequest.Create(restURL.ToString()), HttpWebRequest)

            ' the key line. This adds the base64-encoded authentication information to the request header
            restRequest.Headers.Add("Authorization", "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("USER ID:PASSWORD")))

            restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)
            tDoc = New StreamReader(restResponse.GetResponseStream())

            Dim f As File
            f.WriteAllText("UNC PATH FOR OUTPUT FILE", tDoc.ReadToEnd)


            Dts.TaskResult = Dts.Results.Success

        Catch webEx As WebException

            Dim [error] As New StringBuilder()

            'catch protocol errors

            If webEx.Status = WebExceptionStatus.ProtocolError Then
                [error].AppendFormat("Status code: ", DirectCast(webEx.Response, HttpWebResponse).StatusCode)
                [error].AppendFormat("Status description: ", DirectCast(webEx.Response, HttpWebResponse).StatusDescription)

                ' post the error message we got back.  This is the old error catch code that might work better with SSIS.
                Dts.Events.FireError(0, String.Empty, webEx.Message.ToString(), String.Empty, 0)

                Dts.TaskResult = Dts.Results.Failure
            End If
        End Try

    End Sub

End Class