greenmtnsun

HTTP Authenticatoin Download of File in SSIS to XML file

Recently I posted a script that allows you to download a CSV file to SSIS through HTTP Authentication, today, I'm posting a slightly different script that uses some XML references in order to download an XML file.  You can probably use the other way that I wrote for the CSV file, but since I had both, I thought I'd blog them here for posterity sake. 

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

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
Imports System.Xml

Public Class ScriptMain
    Public Sub Main()
        Try
            Dim restURL As New StringBuilder()
            Dim restRequest As HttpWebRequest
            Dim restResponse As HttpWebResponse
            Dim xDoc As New XmlDocument()

            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_GOES_HERE:PASSWORD_GOES_HERE")))

            restResponse = DirectCast(restRequest.GetResponse(), HttpWebResponse)

            xDoc.Load(restResponse.GetResponseStream())

' If you wanted to, you could change this next line to interact with a variable, just be sure to pass it to the script.
            xDoc.Save("UNC_PATH_AND_FILE_NAME_GO_HERE") 

            xDoc.Save(Console.Out)

            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