SSIS Script to receive data from Web

• bradwilliams51

  SSC-Addicted

  Points: 444

  More actions

  September 19, 2017 at 9:38 am

  #332718

  Hi,

  We currently have an extremely manual process in place where every week I have to export excel files from a provider and then run our packages manually to load the DWH.

  I am looking at automating this process and the provider has provided a URL (PHP, if that info is needed) which needs authentication, the web page then shows the data in XML format.

  I need to retrieve this XML, it doesn't matter if I just copy all the data to an XML file and process it that way, it is just receiving the data that I am having trouble with.  A lot of information I have come across online gives information on how to download a file but it is the webpage that is displaying the XML.

  So far I have created a HTTP Connection Manager with the credentials required (tested successfully) and I am looking at creating a script component to retrieve the data, was trying to follow along to this article: http://dennysjymbo.blogspot.co.uk/2013/05/using-script-component-source-to.html but having difficulty using the Connection Manager and the article doesn't mention authentication.

  Unfortunately due to the nature of the data I am unable to provide a sample or the URL in question, the API document specifies digest authentication.

  Any help will be much appreciated.