Read an RSS feed from SSIS

First published on MSDN on Feb 19, 2009

The 3.5 .NET framework introduced a new http://msdn.microsoft.com/en-us/library/system.servicemodel.syndication.syndicationfeed.aspx class which simplifies the process of reading (and creating) http://en.wikipedia.org/wiki/Rss_feed . To try it out, I threw together a simple SSIS package which reads from an RSS feed using a Script Component.

Things to note:

• I have an Http Connection Manager, but I’m not actually using its connection object in my script. I use it just for its connection string (the URL to the RSS feed)
• You need to add a reference to System.ServiceModel.Web assembly to access the SyndicationFeed class
• The Script Task / Script Component will target the 2.0 .NET framework by default. To use the SyndicationFeed class, you’ll need to use the 3.5 .NET framework. You can change this setting by right clicking on the script component’s Project, and selecting properties…

The code for the script (notice I’m using C# which is only supported in SSIS 2008):

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
private string url = string.Empty;
private SyndicationFeed feed = null;
private XmlReader reader = null;

public override void PreExecute()
{
base.PreExecute();

// Get the URL from the Http Connection Manager.
// Note, we're not actually using the connection manager's connection object,
// just it's URL setting. This is because using the .NET connection classes
// give us more flexibility.
reader = XmlReader.Create(Connections.HttpConnection.ConnectionString);
feed = SyndicationFeed.Load(reader);
}

public override void PostExecute()
{
base.PostExecute();

reader.Close();
}

public override void CreateNewOutputRows()
{
if (feed != null)
{
foreach (var item in feed.Items)
{
Output0Buffer.AddRow();

Output0Buffer.Title = item.Title.Text;
Output0Buffer.PublishDate = item.PublishDate;
Output0Buffer.LastUpdatedTime = item.LastUpdatedTime;
Output0Buffer.Id = item.Id;
Output0Buffer.Summary.AddBlobData(ConvertToBytes(item.Summary));

string authorName = string.Empty;
if (item.Authors.Count > 0)
{
// take the first author
authorName = item.Authors[0].Name;
}
Output0Buffer.Author = authorName;
}

Output0Buffer.SetEndOfRowset();
}
}

private byte[] ConvertToBytes(TextSyndicationContent content)
{
if (content != null && !string.IsNullOrEmpty(content.Text))
{
// convert the string buffer to UTF8 so we can store it in an NTEXT column
var encoding = new UTF8Encoding();
return encoding.GetBytes(content.Text);
}

return new byte[0];
}
}
http://11011.net/software/vspaste

The package is available on my SkyDrive share: