SSIS tutorial: How to get data from table in Notion

Introduction

Notion is a popular workspace platform used by teams to manage projects, documentation, tasks, and databases in one place. Many organisations store operational data in Notion and need to migrate it to SQL Server for reporting, analytics, and business intelligence.

Using Zappysys SSIS PowerPack, you can connect SSIS to Notion REST APIs and extract pages, database records, users, and other objects without writing custom code. This tutorial demonstrates how to connect SSIS to Notion and load data into SQL Server tables using the ZappySys REST API Source.

Prerequisites

1. SQL Server Integration Services (SSIS) is installed.
2. Visual Studio with SSIS extension or SQL Server Data Tools (SSDT).
3. A Notion workspace with at least one database and records.
4. Zappysys SSIS PowerPack is installed on your system.

Steps

Create a Notion Integration and Token

1. Go to Notion Integrations and sign in.
2. Click New integration.
3. Enter a name for your integration and select the target workspace.
4. Click Create connection to proceed.
   

   Notion – Create a new connection

5. Configure the capabilities you need by checking or unchecking the relevant options.
6. Copy the generated Access Token and store it securely.
   

   Notion – Configure new token

7. If you have difficulty creating a new connection, try using the Personal Access Token tab.

Connect your Notion database with your new connection

1. Open your target Notion database page in the browser.
2. Click … in the top-right corner.
3. Click Connections, search for your connection, and select it.
4. Confirm access to the connection.
5. Copy the database ID from the URL. For example:
6. Set URL to:
   
   https://app.notion.com/p/<<DATABASE_ID>>?v=..........

   1	https://app.notion.com/p/<<DATABASE_ID>>?v=..........

   

   Notion – Set a connection to your table

Create an HTTP connection in SSIS

1. Create or open your SSIS project and package.
2. In Connection Managers, right-click and choose 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 Notion Access token in the token value field.
6. Click OK to save the configuration.
   

   Notion – Connection configuration

Create and Configure REST API Source

1. Add a Data Flow Task to the Control Flow and double-click to 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 Notion HTTP connection you created earlier.
5. Set URL to:
   
   https://api.notion.com/v1/databases/{{User::DatabaseId}}/query

   1	https://api.notion.com/v1/databases/{{User::DatabaseId}}/query

6. Set HTTP Request Method to POST.
7. Set Body content type to JSON (application/json).
8. Under headers, add: Notion-Version: {NotionVersion}. (Example: 2022-06-28)
9. Set JSON Path Filter to extract the object array. Example: $.results[*]
10. Click Preview to confirm rows are returned, then click OK to save the configuration.
    

    Notion – JSON source configuration

Configure the destination and run the package

1. Drag and drop a destination component (such as OLE DB Destination, Flat File Destination, or Trash Destination) into the Data Flow.
2. Connect the output arrow from the JSON Source component to the destination component.
3. Double-click the destination component to open its configuration dialogue.
4. Configure the destination to point to your target table or file where Notion database data will be stored.
5. In the Mappings section, map the columns from the JSON response to the corresponding destination columns.
6. Click OK to save the destination configuration.
7. Execute the package and verify that the data extraction is working correctly.
   

   Notion – Final Result

Conclusion

Connecting SSIS to Notion using Zappysys REST API Source enables reliable extraction of workspace data into SQL Server for reporting and analytics. With proper token setup, database sharing, and JSON mapping, you can operationalize Notion data pipelines without custom .NET code.

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 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