SQL Server 2005 & RSS Feed Integration

RSS feed integration

"I (and others on my team, notably Donald Farmer) tend to do a demo showing data from RSS feeds being processed in the SSIS (SQL Server Integration Services) data flow pipeline. I have been asked by quite a few people to post a sample package showing how to incorporate RSS data.

Turns out that it is quite easy to include data from a RSS feed. Try the following:

1. Start a new SSIS project
2. Add a Data Flow Task
3. Switch to the Data Flow tab by double clicking the Data Flow task
4. Add a XML Source to the data flow
5. Double click the XML Source to edit it
6. In the XML Source Editor, in the “Connection Manager” portion, make sure that the “XML File Location” is selected in the Data access mode. In the XML location text box, type in the url for a RSS feed (e.g., http://news.com.com/2547-1_3-0-5.xml)
7. Click the “Generate XSD...” button, give the XSD file a name and save it. Make sure that it is correctly reflected in the XSD location text box.
8. Click “Columns” in the list on the left hand side. If all goes well you should see the available columns from various outputs from the RSS feed.
9. Select “item” from the Output Name dropdown box.
10. Click “OK” to close the XML Source Editor.

Now you are ready to consume the RSS data. ..."

[Content leached so I can easily find it in the future... I'm always afraid that if I just link to it, the original article might go away before I can get back to looking at this...]

It's pretty cool to see how easy it is to integrate RSS feeds, or any XML data source, into SSIS. Man I can think of some cool things to do with this. The first that comes to mind is a cheese Bloglines like service for internal RSS feeds.

Without playing with this (yet) the one concern I have is how well this would work as an ongoing task. i.e. every hour or so... How well will existing/previously retrieved items in the feed are handled, etc. But even if not auto-magically handled I can think of a couple ways to deal with that.

In any case it's a far step beyond the features available in SQL Server 2k...