Question regarding HttpWebRequest in script task

  • Chris Hurlbut

    SSCarpal Tunnel

    Points: 4102

    Hello All,

    I was tasked with getting an xml file from a vendor using a web service.

    I pieced together this code which works BUT I only get the first 500 records

    The problem is there could be 10,000s of records. How do I get the rest of the records?

    Here is what was in the API Documentation:

    If the user is creating their own solution to use the API they will need to implement a paging

    mechanism within their application. If this is not done, the user will only receive the initial 500 records.

    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("RemoteUri").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("Username:password")))

    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("c:\temp\test.xml")

    xDoc.Save(Console.Out)

    Dts.TaskResult = ScriptResults.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 = ScriptResults.Failure

    End If

    End Try

    End Sub

    Any help would be appreciated.

  • frederico_fonseca

    SSChampion

    Points: 14755

    you already have your answer - you need to create a paging mechanism, and the API documentation should state how that is done.

    And without us knowing what is the product in question we can't guess how it is done.

    your best option is to go and talk with the vendor or ask on their forums.

  • Chris Hurlbut

    SSCarpal Tunnel

    Points: 4102

    Thanks for your confirmation, I did stumble upon the answer knee deep in the documentation.

    I have to get one record, get a xml node that contains the total records, then call the service for each set of 500 records.

    fun stuff:(

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply