August 18, 2016 at 12:13 pm
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.
August 18, 2016 at 1:06 pm
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.
August 18, 2016 at 1:28 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply