SSIS Tutorial: How to get data from ShipStation

Introduction

ShipStation is a leading shipping and order management platform used by eCommerce businesses to manage orders, shipments, and fulfillment across multiple sales channels. Many organizations need ShipStation data in SQL Server for reporting, logistics analytics, and fulfillment performance tracking.

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

Steps

Get ShipStation API Credentials

1. Sign in to your ShipStation account.
2. Go to Account Settings → API Settings.
   

   ShipStation – Settings

3. Generate your API Key and API Secret — store both values securely.
   

   ShipStation – Generate token

   1

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 Credentials Type to Basic Auth.
5. Enter your API Key in the Username field and your API Secret in the Password field.
6. Click OK to save the connection.
   

   ShipStation – 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 ShipStation HTTP connection you created earlier.
5. Set the API URL. Example to extract orders:
   https://ssapi.shipstation.com/orders

   1	https://ssapi.shipstation.com/orders

6. Set the HTTP Request Method to GET.
7. Add the following HTTP Header:
   • Accept: application/json
8. Set JSON Path Filter based on the endpoint. Each ShipStation endpoint wraps its array under a named key — for example:
   • Orders: $.orders[*]
   • Shipments: $.shipments[*]
   • Products: $.products[*]
   • Customers: $.customers[*]
9. Click Preview to confirm rows are returned, then click OK.
   

   ShipStation – JSON Source configuration

Configure Destination Component

1. Add an Upsert Destination (or another destination of your choice, such as a Trash destination or 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. Select the options you want to use: Insert and/or Update.
5. Map the source columns to destination columns in the Mappings section.
   

   ShipStation – Upsert destination

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

   ShipStation – Upsert Destination example

Conclusion

You now have a working SSIS integration to extract ShipStation data using ZappySys SSIS PowerPack and the ShipStation REST API. With this pattern, you can automate extraction of orders, shipments, products, and customer data into SQL Server for reporting and fulfillment analytics.

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