SSIS tutorial: How export data from Pipedrive

Introduction

Pipedrive is a popular CRM platform used by sales teams to manage deals, leads, contacts, activities, and pipelines. It offers REST APIs that let you access CRM data programmatically. Many organisations extract Pipedrive data into SQL Server for operational reporting, sales dashboards, and analytics.

In this tutorial, you will learn how to connect Pipedrive 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 Pipedrive account with API access.
4. A valid Pipedrive API token.
5. ZappySys SSIS PowerPack is installed on your machine.

Steps

Generate Pipedrive API Token

1. Sign in to your Pipedrive account.
2. Go to Personal Preferences > API.
3. Copy your API token and store it securely.
   

   Pipedrive – Personal token

Create and Configure JSON Source

1. In your SSIS package, navigate to the Variables section and create the following variables: PersonalToken (String) — Enter the Personal Token from the previous step.
2. Drag a Data Flow Task into Control Flow and open it.
   

   Drag and drop Data flow

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

   SSIS JSON Source – Drag and Drop

4. Open the JSON Source
5. Set the API URL with the variable. Example to extract deals:
   
   https://api.pipedrive.com/v1/deals?api_token={{User::PersonalToken}}

   1	https://api.pipedrive.com/v1/deals?api_token={{User::PersonalToken}}

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.
   

   Pipedrive – JSON source configuration

Use Other Pipedrive Endpoints (Optional)

You can reuse the same SSIS package pattern with other Pipedrive endpoints:

• Persons: https://api.pipedrive.com/v1/persons?api_token={your_api_token}
• Organizations: https://api.pipedrive.com/v1/organizations?api_token={your_api_token}
• Activities: https://api.pipedrive.com/v1/activities?api_token={your_api_token}
• Leads: https://api.pipedrive.com/v1/leads?api_token={your_api_token}
• Pipelines: https://api.pipedrive.com/v1/pipelines?api_token={your_api_token}

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.
   

   Pipedrive  – Upsert destination

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

   Pipedrive  – Upsert Destination example

Conclusion

You now have a working SSIS integration to extract Pipedrive data using ZappySys SSIS PowerPack and Pipedrive REST APIs. With this pattern, you can automate extraction of deals, contacts, and activity data into SQL Server for reporting and 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.

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