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()
Dim restURL As New StringBuilder()
Dim restRequest As HttpWebRequest
Dim restResponse As HttpWebResponse
Dim xDoc As New XmlDocument()
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)
' If you wanted to, you could change this next line to interact with a variable, just be sure to pass it to the script.
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
Any help would be appreciated.