How to read RSS feed in SSIS and ODBC (with pagination)

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 feedsApple RSS feedsSamsung 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

  1. First of all, ZappySys SSIS Powerpack installed.
  2. Secondly, SSDT for Business Intelligence (SSIS) installed.
  3. Finally, ODBC Powerpack installed.

Getting Started

Understanding RSS data

  1. In order to start, we will work with the WordPress RSS example:
  2. When you go to the link, you see the feed like this:
    RSS data

    RSS information

  3. 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

  1. 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.
    Use Data Flow

    Drag and drop Data Flow

  2. Secondly, In the ZS XML Source, add the following RSS WordPress URL:
    URL of RSS in SSIS

    SSIS read RSS

  3. After that, you will be able to see the following tags when you press the array filter:
  4. This time, we will focus on the information under channel which is the most relevant, make sure that the array filter is this one:
    Filter RSS data in SSIS

    Using RSS filters in SSIS

  5. In addition, press the preview data button and you will see the following information:
    SSIS Preview Data

    SSIS preview RSS

Read RSS feed in SSIS and copy to SQL Server

  1. In the previous section, we got data from RSS. Now we will copy to SQL Server.
  2. First of all, drag and drop the OLEDB Destination and join the XML Source with the OLEDB Destination:
    Copy data from RSS to SQL Server

    SSIS RSS to OLEDB

  3. Also, in the OLEDB Destination, press New button and select a SQL Server connection or create a new one:
    Create oledb destination table

    RSS to Oledb

  4. In addition, in the table or view textbox press new to create a new destination table:
    Create SQL Server to store RSS data in SSIS

    Create a new SQL table to store RSS data

  5. Next, modify the table name:
    SQL Server table name in SSIS

    Change SSIS destination table name

  6. Also, go to the mappings page to generate the mappings:
    SSIS with RSS to SQL Server

    RSS Source to SQL Destination in SSIS

  7. 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.

  1. First of all, in the URL, you can include the following to specify the page:
  2. Also, note that it used the word paged and not page. Many URLs use page and not paged, but WordPress is different:
  3. In addition, if you want to use variables, you can use a variable like this:
    RSS pagination in SSIS
    RSS page as a variable
  4. Also, we have a pagination tab with different pagination options. You can check the configuration here:
    Read RSS feed in SSIS

    RSS pagination configuration

  5. 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.

  1. First of all, open your ODBC Data Source Administrator in Windows.
  2. Secondly, in the user DSN, press Add.
  3. Also, add the Zappysys XML Driver:
    Connect to RSS using ODBC

    odbc XML driver

  4.  In addition, add the following URL:
    URL of RSS

    ODBC to RSS connection

  5. Finally, you can use the filters and pagination as we did with the SSIS PowerPack in the previous sections.
    Use pagination for RSS

    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:

Posted in ODBC PowerPack, SSIS XML Source (File / SOAP), XML File / SOAP API Driver and tagged , , .