SSIS Tutorial: How to get data from Cin7

Introduction

Cin7 is a connected inventory and order management platform used by growing businesses to synchronise products, stock, orders, and customer data across multiple sales channels. Many organisations need Cin7 data in SQL Server for reporting, demand planning, fulfilment tracking, and analytics.

In this tutorial, you will learn how to connect to Cin7 REST APIs 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 Cin7 API Credentials

1. Sign in to your Cin7 account.
2. Open Settings → Integrations & API → API v1 in your Cin7 admin portal.
   • Note your API Username shown on the page.
   • Click Add New API Connection, give it a name (e.g. ZappySys), and copy the generated API Key.
   

   Cin7 – Generate new API connection

3. Store both values securely — the API Username and API Key together act as your login credentials.
   

   Cin7 – API credentials

Get Cin7 Base URL and Endpoint Path

1. Identify the REST endpoint you want to query (for example, products, customers, or sales orders).
2. Keep your API base URL and endpoint path ready for SSIS configuration.
3. Example:
   https://api.cin7.com/api/v1/Products

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

   Cin7-HTTP connection

Create and Configure JSON Source

1. Drag a Data Flow Task into Control Flow and open it.
2. In Data Flow, drag and drop the ZS JSON Source.
3. Open the JSON Source editor and enable Use credentials.
4. Select the Cin7 HTTP connection you created earlier.
5. Set the API URL. Example to extract products:
   
   https://api.cin7.com/api/v1/products

   1	https://api.cin7.com/api/v1/products

6. Set HTTP Request Method to GET.
7. Add the following HTTP Header:
   1. Accept: application/json
8. Set JSON Path Filter based on the endpoint (for example, $.Products[*] for the Products endpoint).
9. Click Preview to confirm rows are returned, then click OK.
   

   Cin7 – 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. Check the options you want to use: Insert or/and Update.
5. Map the source columns to destination columns in the Mappings section.
   

   Cin7 – Upsert destination

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

   Cin7 – Upsert Destination example

Conclusion

You now have a working SSIS integration to extract Cin7 data using ZappySys SSIS PowerPack and Cin7 REST APIs. With this pattern, you can automate extraction of inventory, product, and order 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 transformations, data validation, error handling, and scheduling through SQL Server Agent.