SSIS Tutorial: How to get data from Airtable

Introduction

Airtable is a cloud-based platform that combines spreadsheet simplicity with database capabilities. Teams use Airtable to manage projects, operations, CRM workflows, and content pipelines. Many organisations need Airtable data in SQL Server for reporting, analytics, and downstream data integration.

In this tutorial, you will learn how to connect to Airtable 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 Airtable Personal Access Token

1. Sign in to your Airtable account.
2. Go to your developer hub and create a Personal Access Token.
3. Grant scopes required for reading records (for example, data.records:read and schema.bases:read based on your use case).
4. Allow access to the target base(s) and securely copy the token.
   

   Airtable – Create a token

Get Airtable Base ID and Table ID

1. Open the target table in Airtable.
2. Copy the Base ID and Table ID from the URL or API documentation page.
3. Keep these IDs handy because you will use them in the API URL, for example:
   https://api.airtable.com/v0/{BASE_ID}/{TABLE_ID}

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 Static token/API key.
5. Paste your Airtable token in the token value field.
6. Click OK to save the connection.
   

   Airtable –  Connection configuration

Create and Configure JSON Source

1. In your SSIS package, navigate to the Variables section and create the following variables:
   1. BaseID(String) — Enter the Base ID.
   2. TableID (String) — Enter the Table ID.
2. Drag a Data Flow Task into Control Flow and open it.
   

   Dragging and dropping Data Flow Task into Control Flow

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

   SSIS JSON Source – Drag and Drop

4. Open the JSON Source editor and enable Use credentials.
5. Select the Airtable HTTP connection you created earlier.
6. Set the API URL. Example to extract records from a table:
   
   https://api.airtable.com/v0/{{User::BaseID}}/{{User::TableID}}

   1	https://api.airtable.com/v0/{{User::BaseID}}/{{User::TableID}}

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

   1	$.records[*]

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

   Airtable – JSON Source configuration

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.
   

   Airtable – Upsert destination

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

   Airtable – Upsert Destination example

Conclusion

You now have a working SSIS integration to extract Airtable data using ZappySys SSIS PowerPack and Airtable APIs. With this pattern, you can automate extraction of operational records 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.