Extracting RSS Feed Data in ETL Process using KingswaySoft
It’s a very common practice to receive news updates using RSS feeds. Even though users are shifting to social media for getting news and headlines that are prominent, these platforms are controlled by algorithms that decide what news goes to which user, and users may see the headlines which the platform decides. RSS feeds provides all the contents that are published by the sources that can be subscribed to. Now, when it comes to integration, extracting RSS feeds data can be a challenging task without a proper tool. In our blog post here today, we will show you could leverage KingswaySoft SSIS Productivity Pack to achieve this with ease, so that you could possibly consume the data in your ETL process with great control.
Initial requirements
RSS feeds are in XML format, and to retrieve the RSS feed information, you will need the HTTP Connection Manager combined with the XML Source component, both found in our SSIS Productivity Pack (download link here). Please find below the Online Help Manual for the above components.
You will also need the URL for the RSS feed that you are reading from. For the examples below, we are using a public news feed (http://rss.cnn.com/rss/cnn_world.rss). You can find many such public feeds if you don’t have a specific one currently in mind.
Configuration
We will start by configuring our connection manager. This involves using the RSS feed’s url as the Base URL as shown below.
Since this is a public feed, we do not have any Authentication to enter. If you do need to authenticate your connection, you would do so in the Authentication tab. Once this is set, you can click “Test Connection”, and then “Test Connection” again on the window that pops up, and you should see the RSS feed’s XML in the Response Body:
Once the HTTP Connection Manager is configured, you can drag an XML Source component to the Data Flow Task and open it. From the Connection Manager dropdown on the Data Source page, select the HTTP Connection that you just created. If you need a relative path, or any query string parameters or request headers, you can add them here. For the RSS Feeds we are reading from, none of those are needed:
In the Document Designer, click on the Import button in the bottom right of the window and select “XML (Web)”. This will use the HTTP Connection Manager to import the RSS’s xml into our Document Designer.
In the Columns page, you can see what columns will be parsed out from the xml. There are the “rss” element’s columns, and under that there is "channel", "item" and "group". And if you click the dropdown arrow (see the second image below), you can select “item” to see the columns that will contain values for each RSS feed item.
Managing the RSS Outputs
Now you have 4 outputs that you can send to components downstream:
The outputs, if you enable a Data Viewer, will look like the following. Note that the "Item" output would be of use to get the news items.
You could also get the other outputs as well, optionally, if you wish to get the "rss", "channel" and "group" outputs for any integration requirements. And if you wish to join these outputs together, you could use the _Rowindex and _ParentKeyField that are generated by our component and use the sort and merge out of the component, or our Premium Lookup component, as shown in another blog post that we have (Link here).
Conclusion
The KingswaySoft component can thus be easily configured to get the RSS feeds. The above idea doesnt limit a simple RSS feed, and the document designer in the XML Source component be modified to suite complex XML outputs as well.