Write/Read WordPress data using SSIS and REST API

Introduction

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, Facebook, Gmail, etc).

Requirements

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:

• ZappySys PowerPack for SSIS Installer
• SSDT for Business Intelligence

Getting started

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

	SSIS REST API Web Service Task
	SSIS JSON Source (File, REST API, OData Connector)
	SSIS JSON File Destination (Create JSON File)

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:
   
   
   http://demo.wp-api.org/wp-json/wp/v2/pages

   1	http://demo.wp-api.org/wp-json/wp/v2/pages

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:
     
     http://demo.wp-api.org/wp-json/wp/v2/tags

     1	http://demo.wp-api.org/wp-json/wp/v2/tags

   • In order to read comments in WordPress using SSIS and REST API, use this URL:
     
     http://demo.wp-api.org/wp-json/wp/v2/comments

     1	http://demo.wp-api.org/wp-json/wp/v2/comments

   • For the categories, use this URL to read WordPress data using SSIS:
     
     http://demo.wp-api.org/wp-json/wp/v2/categories

     1	http://demo.wp-api.org/wp-json/wp/v2/categories

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:
   

   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:
   

   Data Flow Task

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

   JSON Source to WordPress API

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

   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
   
   URL: http://demo.wp-api.org/wp-json/wp/v2/categories

   1	URL: http://demo.wp-api.org/wp-json/wp/v2/categories

2. Secondly, we need to convert the JSON output to the following format:
   
   { "id" : { "count" : 1474359596826126768, "description" : { "link" : "xxx" } } }

   1 2 3 4 5 6 7 8 9 10

   {    "id" :      {          "count" : 1474359596826126768,          "description" :           {             "link" : "xxx"           }     } }

3. Next, we will add an element first:
   

   SSIS WordPress API

4. Also, we will select the id attribute:
   

   id attributes SSIS

5. In addition, we will add a static element to the id:
   

   Add JSON element

6. Furthermore, we will add the count element.
   

   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:
   
   http://demo.wp-api.org/wp-json/wp/v2/posts?per_page=2

   1	http://demo.wp-api.org/wp-json/wp/v2/posts?per_page=2

   

   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.
   

   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:
   
   POST http://demo.wp-api.org/wp-json/wp/v2/posts/

   1	POST http://demo.wp-api.org/wp-json/wp/v2/posts/

2. Secondly, we have the post with id equal to 2 and we will use the body request as follows:

Conclusion

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!

References

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

• REST API Handbook
• WordPress REST API Tutorial (Real Examples)