Introduction
Read RSS feed in SSIS can be challenging. RSS named first RDF Site Summary and later named Rich Site Summary and Really Simple Syndication allows customer applications to be updated with the news of a site. For example, Microsoft RSS feeds, Apple RSS feeds, Samsung RSS feeds, etc. With RSS the information comes to you directly and you do not need to waste time checking the web site. In this article, we will show how to read RSS feed in SSIS and later using ODBC.
Requirements
- First of all, ZappySys SSIS Powerpack installed.
- Secondly, SSDT for Business Intelligence (SSIS) installed.
- Finally, ODBC Powerpack installed.
Getting Started
Understanding RSS data
- In order to start, we will work with the WordPress RSS example:
1https://wordpress.org/news/feed/ - When you go to the link, you see the feed like this:
RSS information
- Basically, it is an XML file with the Last Build date (lastBuildDate), the title, link, description. It shows the WordPress news per version and date.
Read RSS feed in SSIS
- First of all, you need an SSIS Project and a Data Flow with the ZS XML Source (if you are not familiar with SSIS and the ZS XML Source, refer to this link.
Drag and drop Data Flow
- Secondly, In the ZS XML Source, add the following RSS WordPress URL:
1https://wordpress.org/news/feed/
SSIS read RSS
- After that, you will be able to see the following tags when you press the array filter:
- This time, we will focus on the information under channel which is the most relevant, make sure that the array filter is this one:
1$.rss.channel[*].item[*]
Using RSS filters in SSIS
- In addition, press the preview data button and you will see the following information:
SSIS preview RSS
Read RSS feed in SSIS and copy to SQL Server
- In the previous section, we got data from RSS. Now we will copy to SQL Server.
- First of all, drag and drop the OLEDB Destination and join the XML Source with the OLEDB Destination:
SSIS RSS to OLEDB
- Also, in the OLEDB Destination, press New button and select a SQL Server connection or create a new one:
RSS to Oledb
- In addition, in the table or view textbox press new to create a new destination table:
Create a new SQL table to store RSS data
- Next, modify the table name:
Change SSIS destination table name
- Also, go to the mappings page to generate the mappings:
RSS Source to SQL Destination in SSIS
- Finally, run the package and you will export the data from RSS to SQL Server.
How to read RSS feed in SSIS using pagination
If you have multiple rows, pagination helps a lot to handle multiple items. You can handle the information in pages. Instead of loading all the information, you can get the data from page 1, 2, 3 and more.
- First of all, in the URL, you can include the following to specify the page:
1https://wordpress.org/news/feed/?paged=2 - Also, note that it used the word paged and not page. Many URLs use page and not paged, but WordPress is different:
- In addition, if you want to use variables, you can use a variable like this:
RSS page as a variable - Also, we have a pagination tab with different pagination options. You can check the configuration here:
RSS pagination configuration
- Finally, for more information about pagination, refer to this link.
How to read RSS feed in ZappySys ODBC PowerPack
Zappysys ODBC PowerPack is a powerful tool to access to REST API, SOAP, XML, JSON, CSV, Odata. In this article, we will show how to access to RSS using ODBC PowerPack.
- First of all, open your ODBC Data Source Administrator in Windows.
- Secondly, in the user DSN, press Add.
- Also, add the Zappysys XML Driver:
odbc XML driver
- In addition, add the following URL:
1https://wordpress.org/news/feed/
ODBC to RSS connection
- Finally, you can use the filters and pagination as we did with the SSIS PowerPack in the previous sections.
ODBC pagination RSS
Conclusion
To conclude, we can say that with SSIS PowerPack and ODBC PowerPack, it is easy to access, filter and paginate RSS information or any XML / SOAP API. We strongly recommend you to try the product by yourself and check the results.
References
For more information about RSS, refer to these links:
