SSIS tutorial: How to get data from WooCommerce

Introduction

WooCommerce is one of the most popular eCommerce platforms for WordPress. It provides REST APIs to access products, orders, customers, coupons, and other store data. Many teams need to migrate WooCommerce data to SQL Server for reporting, operational dashboards, and analytics.

In this tutorial, you will learn how to connect WooCommerce APIs to SQL Server Integration Services (SSIS) using ZappySys SSIS PowerPack. This approach helps you build repeatable ETL pipelines without writing custom API integration code.

Prerequisites

1. SQL Server Integration Services (SSIS) is installed.
2. Visual Studio with SSIS extension or SQL Server Data Tools (SSDT) is available.
3. A WordPress site with WooCommerce installed and API access enabled.
4. ZappySys SSIS PowerPack is installed on your machine.

Steps

Generate WooCommerce API Credentials

1. Sign in to your WordPress admin dashboard.
2. Go to WooCommerce > Settings > Advanced > REST API.
3. Click Add key.
4. Enter a description (for example, SSIS Integration), select a user, and set permissions to Read (or Read/Write if needed).
5. Click Generate API key.
6. Copy the Consumer Key and Consumer Secret, then store them securely.
   

   Woocommerce – Get API key

Create an HTTP Connection in SSIS

1. Create or open your SSIS project and package.
2. In Connection Managers, right-click and select New Connection….
3. Select ZS-HTTP from the SSIS connectors list.
   

   Create a new HTTP connection

4. Set the API URL:
   
   https://{your-website}.com/wp-json/wc/v3

   1	https://{your-website}.com/wp-json/wc/v3

5. Set Credentials Type to Basic Auth (Username/Password).
6. Set Username to your WooCommerce Consumer Key.
7. Set the Password to your WooCommerce Consumer Secret.
8. Click OK to save the connection.
   

   Woocommerce – the HTTP connection

Create and Configure JSON Source

1. Drag a Data Flow Task into Control Flow and open it.
   

   Drag and drop Data flow

2. In Data Flow, drag and drop the ZS JSON Source.
   

   SSIS JSON Source – Drag and Drop

3. Open the JSON Source editor and enable Use credentials.
4. Select the WooCommerce HTTP connection you created earlier.
5. Set the API URL. For example, use the following endpoint to fetch a summary of deposits:
   
   https://{your-website}.com/wp-json/wc/v3/payments/deposits

   1	https://{your-website}.com/wp-json/wc/v3/payments/deposits

6. Set HTTP Request Method to GET.
7. Set JSON Path Filter to:
   
   $.data[*]

   1

   $.data[*]

8. Click Preview to confirm rows are returned, then click OK.
   

   Woocommerce – JSON source

Configure Destination Component

1. Add an Upsert destination (or another destination of your choice, such as a Trash destination or a JSON/CSV/XML destination).
2. Connect the output arrow from JSON Source to the destination component.
3. Configure your SQL Server connection and target table.
4. Check the options you want to use: Insert or/and Update
5. Map the source columns to destination columns in the Mappings section.
   

   Woocommerce  – Upsert destination

6. Click OK and execute the package.
7. Validate that WooCommerce rows have been loaded into your SQL Server table.
   

   Woocommerce  – Upsert Destination example

Conclusion

You now have a working SSIS integration to extract WooCommerce data using ZappySys SSIS PowerPack and WooCommerce REST APIs. With this pattern, you can automate the extraction of product, order, and customer data into SQL Server for reporting and analytics.

Explore the full capabilities of the ZappySys SSIS PowerPack to optimise your data integration tasks. By applying this pattern, you can extend your solution to include additional transformations, data validation, error handling, and scheduling through SQL Server Agent.

Need Help?

If you encounter any issues, our support team is here to help:

1. Live Chat — Use the chat widget (bottom-right corner of this page)
2. Email — support@zappysys.com
3. Support Center — Visit the ZappySys Support Portal