Can SSIS read data from a hyperlink?

• Harold J Hunsaker-402851

  Old Hand

  Points: 300

  More actions

  July 23, 2007 at 12:24 pm

  #117954

  Hello,

  Is it possible to pull data from a hyperlink using SSIS?

  I know SSIS has a web services task, but that requires a WSDL file, and the site I am connecting to does not have one, nor are the data in XML format.

  But id I click the link, the broswer disaplays the data I need.

  Does anyone know if this is possible, and if so, hoe it might be done?

  Thanks for you help and time,

  Harold

   

   

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  July 24, 2007 at 2:02 am

  #721798

  What exactly is the data format? If you asking can SSIS parse a web page....

  If the data is actually a text file that IE is rendering, then you can use a script component to download the page to a temp directory and use it as normal from there.

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• Harold J Hunsaker-402851

  Old Hand

  Points: 300

  More actions

  July 24, 2007 at 7:50 am

  #721916

  Hello Crispin,

  Thanks for the suggestion. I posted this question on an MSDN board, and received a similar suggestion to use a script task.

  The web page I want to read ouputs a JSON string.

  The code that follows was provided by MSDN boear member JayH:

          Dim webRequest As System.Net.WebRequest = System.Net.HttpWebRequest.Create(Dts.Variables("Url").Value.ToString())

          Dim webResponse As System.Net.WebResponse = webRequest.GetResponse()

          Dim stream As System.IO.Stream = webResponse.GetResponseStream()

          Dim streamReader As New System.IO.StreamReader(stream)

          Dim content As String = streamReader.ReadToEnd()

  I replaced the URL and it works perfect

  Thanks for your help!

  Harold