Loading XML data from a URL in SSIS

Loading XML data from a web endpoint in SSIS is easier than it seems.  Create a Data Flow Task and add a XML Source Task.  While it appears the task is looking for an XML file path, a URL can be plugged into the XML location.  Generate the XSD file locally and then wire up transformations or destinations to the output.

image

Advertisements
This entry was posted in SQL Server Integration Services and tagged , , , , . Bookmark the permalink.

3 Responses to Loading XML data from a URL in SSIS

  1. Bryan says:

    Thanks for this. I am able to do this in VS2008 no problem. But I have moved to VS2010 now. It manages to retrieve the nodes and creates the XSD but when I try to execute the script, it complains that it can’t find the file “http://api……”. Any ideas what I’m doing wrong? Worked perfectly in VS2008.

  2. Matt Massey says:

    “While it appears the task is looking for an XML file path, a URL can be plugged into the XML location.” Thank you so much!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s