Write/Read WordPress data using SSIS and REST API


In this new article, we will show how to read WordPress data using REST API. REST API is a very popular software architectural style for Web Services.
SSIS is a very popular ETL software used to integrate almost any data (SQL Server, Oracle, Excel, DB2, CSV files, Big Data, FacebookGmail, etc).


For this example, we will use a popular plug-in. The name is ZappySys PowerPack for SSIS which includes some nice generic connectors to REST API. You can download it here:

Getting started

We will be using this ZappySys SSIS PowerPack component to make things work:

Read WordPress data using SSIS with REST API

REST API is also the name of one of the ZappySys components. These tools can connect to thousands of REST API Servers. In this example, we will connect to WordPress.

  1. First, drag and drop the REST API task to the design pane.
  2. Secondly, write the following URL in the REST API task URL:

  3. After that, press Test Request/Response.
  4. That URL shows the page’s information in WordPress. This is a demo API. For more information about the WordPress links, check here.

    Call WordPress in SSIS

  5. Next, you can also check the Tags, Comments, Categories, Posts, Taxonomies, Media. Here you have some examples:
    • To get the tags in WordPress, use this URL:
    • In order to read comments in WordPress using SSIS and REST API, use this URL:
    • For the categories, use this URL to read WordPress data using SSIS:
  6. In addition, for your blog, you need to add /wp-json/wp/v2/ and pages, posts or tags in your WordPress URL.
  7. Finally, if you want to save the results, in Response Settings, enable save response content and select the option save to file, and specify a file path:
    Wordpress response into file

    Save response WordPress API

How to read WordPress data using SSIS and store it into a SQL Server database

In the next example, we will use the JSON Source component. This component is similar to the REST API, but this is used in the Data Flow to save the REST API results in any destination of your preference (SQL Server, PostgreSQL, MongoDB, etc). Also, this example will read WordPress data using SSIS and store it into a SQL Server database.

  1. First of all, in the Control flow, drag and drop the Data Flow:
    Insert Data Flow

    Data Flow Task

  2. Drag and drop the JSON Source in the Data flow with this configuration:
    SSIS wordpress data

    JSON Source to WordPress API

  3. Finally, you can save to an OLEDB Destination to save into a SQL Database:
    From WordPress into SQL Server

    Store WordPress into SQL Server

How to read WordPress data using SSIS and modify the JSON structure

Sometimes we need to modify the WordPress JSON output to a custom output in SSIS. We will now use the JSON Generator Transform this time, which is a very powerful tool for this purpose.

  1. First of all, we will use the JSON Source connecting to the following
  2. Secondly, we need to convert the JSON output to the following format:
  3. Next, we will add an element first:
    Add custom JSON in SSIS

    SSIS WordPress API

  4. Also, we will select the id attribute:
    SSIS id JSON

    id attributes SSIS

  5. In addition, we will add a static element to the id:
    Increment an element in JSON with SSIS

    Add JSON element

  6. Furthermore, we will add the count element.
    SSIS generate JOSN

    Add element in JSON with SSIS

  7. Finally, repeat the same process for the description and link. The JSON structure will be the following:

How to read WordPress data using SSIS and use pagination

If it is a lot of data in REST API, pagination allows showing the results in pages. Let’s take a look at it.

  1. First of all, we will use page 2. To do it, we will use the following URL in the JSON Source task in the data flow:
    Wordpress Pagination SSIS

    SSIS URL pagination REST API

  2. Secondly, there is a pagination tab that can be used to get the data using pagination. For more information about pagination, refer to our ZappySys Pagination article related.
    Pagination handled in SSIS for WordPress

    SSIS pagination in SSIS

How to write WordPress data using SSIS

  1. In order to write WordPress data using SSIS, we will use the REST API task with the following URL:
  2. Secondly, we have the post with id equal to 2 and we will use the body request as follows:


To conclude, in this article, we learned how to write and read WordPress data using SSIS. In addition, we learned also how to get pagination and how to change the format of the JSON results. If you want to try it yourself, do not hesitate to download ZappySys PowerPack!


Finally, for more information about how to read WordPress Data using SSIS, refer to these links:

Posted in REST API Integration, SSIS JSON Generator Transform, SSIS JSON Source (File/REST) and tagged , , , .