Hubspot Connector for SSIS Hubspot Connector can be used to integrate Hubspot and your data source, e.g. Microsoft SQL Server, Oracle, Excel, Power BI, etc. Get, write, delete Hubspot data in a few clicks! In this article you will learn how to quickly and efficiently integrate Hubspot data in SSIS without coding. We will use high-performance Hubspot Connector to easily connect to Hubspot and then access the data inside SSIS. Let's follow the steps below to see how we can accomplish that! Download Documentation Hubspot Connector for SSIS is based on ZappySys API Connector Framework which is a part of SSIS PowerPack. It is a collection of high-performance SSIS connectors that enable you to integrate data with virtually any data provider supported by SSIS, including SQL Server. SSIS PowerPack supports various file formats, sources and destinations, including REST/SOAP API, SFTP/FTP, storage services, and plain files, to mention a few (if you are new to SSIS and SSIS PowerPack, find out more on how to use them).

Connect to Hubspot in other apps Connect to Hubspot in SQL Server Connect to Hubspot in Power BI Connect to Hubspot in SSRS Connect to Hubspot in Informatica Connect to Hubspot in MS Access Connect to Hubspot in MS Excel Connect to Hubspot in SSAS Connect to Hubspot in C# Connect to Hubspot in Python Connect to Hubspot in JAVA Connect to Hubspot in Tableau Connect to Hubspot in SAP Crystal Reports Connect to Hubspot in Azure Data Factory (Pipeline) Connect to Hubspot in Azure Data Factory (SSIS) Connect to Hubspot in Talend Studio Connect to Hubspot in UiPath Connect to Hubspot in PowerShell Connect to Hubspot in ODBC

Video Tutorial - Integrate Hubspot data in SSIS

This video covers the following topics and more, so please watch carefully. After watching the video, follow the steps outlined in this article:

• How to download and install the required PowerPack for Hubspot integration in SSIS
• How to configure the connection for Hubspot
• Features of the ZappySys API Source (Authentication / Query Language / Examples / Driver UI)
• How to use the Hubspot in SSIS

Prerequisites

Before we begin, make sure the following prerequisites are met:

1. SSIS designer installed. Sometimes it is referred as BIDS or SSDT (download it from Microsoft).
2. Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
3. SSIS PowerPack is installed (if you are new to SSIS PowerPack, then get started!).

Read data from Hubspot in SSIS (Export data)

1. Begin with opening Visual Studio and Create a New Project.

2. Select Integration Service Project and in new project window set the appropriate name and location for project. And click OK.

3. In the new SSIS project screen you will find the following:

   1. SSIS ToolBox on left side bar
   2. Solution Explorer and Property Window on right bar
   3. Control flow, data flow, event Handlers, Package Explorer in tab windows
   4. Connection Manager Window in the bottom
   
   Note: If you don't see ZappySys SSIS PowerPack Task or Components in SSIS Toolbox, please refer to this help link.

4. Now, Drag and Drop SSIS Data Flow Task from SSIS Toolbox. Double click on the Data Flow Task to see Data Flow designer.

   

5. From the SSIS toolbox drag and API Source (Predefined Templates) on the data flow designer surface, and double click on it to edit it:
   

6. Select New Connection to create a new connection:
   

7. Use a preinstalled Hubspot Connector from Popular Connector List or press Search Online radio button to download Hubspot Connector. Once downloaded simply use it in the configuration:
   

   Hubspot

   

8. Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

    Private app (Static Access Token) [Http]

   Private apps allow you to use HubSpot's APIs to access specific data from your HubSpot account. You can authorize what each private app can request or change in your account, which will generate an access token that's unique to your app. [API reference]

   Steps how to get and use Hubspot credentials
   

   Follow these instructions to create a Private app in HubSpot and configure HubSpot authentication in SSIS package or ODBC data source:

   1. First of all, go to HubSpot portal.
   2. Then click Data Management option on the left and then hit Integrations menu item:
   3. Move on by clicking Private Apps menu item and then click Create a private app button to create one:
   4. Then continue by following the instructions on HubSpot Docs.
   5. Once you create an app, go to SSIS package or ODBC data source and in Private App authentication set these parameters:
      • For AccessToken field use Access token value from the Private app configuration in HubSpot.
   6. Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Private app (Static Access Token) [Http]

   https://api.hubapi.com

   Required Parameters
   AccessToken	Fill-in the parameter...
   Optional Parameters
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

    Public app [OAuth]

   In HubSpot, a public app is a type of integration that can be installed on customer accounts or listed on the App Marketplace. It requires authentication via OAuth. Once a user installs your app on their HubSpot account, you’ll be able to make API calls to that account using an OAuth access token. [API reference]

   Steps how to get and use Hubspot credentials
   

   Follow these instructions to create a Public app in HubSpot and configure HubSpot authentication in SSIS package or ODBC data source:

   1. First of all, if you don't have HubSpot Developer's account, register one.
   2. Go to HubSpot and select your Developer account.
   3. Click Apps on the left hand side and then click Create app button to create a Public app:
   4. Then continue by following the instructions in HubSpot Docs.
      NOTE: If you don't have a working Redirect URL, you can use https://zappysys.com/oauth (it's safe).

   5. Once you create an app, go to SSIS package or ODBC data source and in Public app authentication set these parameters (use values from your Public app configuration in HubSpot):
      • For ClientId field use Client ID value.
      • For ClientSecret field use Client secret value.
      • For Scope field use Scopes values, e.g.:

        accounting
        tickets
        files
        
        crm.lists.read
        crm.lists.write
        
        crm.objects.contacts.read
        crm.objects.contacts.write
        crm.schemas.contacts.read
        
        crm.objects.custom.read
        crm.objects.custom.write
        crm.schemas.custom.read
        
        crm.objects.companies.read
        crm.objects.companies.write
        crm.schemas.companies.read
        
        crm.objects.deals.read
        crm.objects.deals.write
        crm.schemas.deals.read
        
        crm.objects.quotes.read
        crm.objects.quotes.write
        crm.schemas.quotes.read
        
        crm.objects.line_items.read
        crm.objects.line_items.write
        crm.schemas.line_items.read
        

        NOTE: You can use other scopes too, but they must match the scopes defined in your Public app.

      • For ReturnUrl field use one of Redirect URLs.
   6. When you are done with that, click Generate Token button to generate Access and Refresh Tokens.
   7. Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Public app [OAuth]

   https://api.hubapi.com

   Required Parameters
   UseCustomApp	Fill-in the parameter...
   Scope (Must Match with App Scopes)	Fill-in the parameter...
   Optional Parameters
   Client ID
   Client Secret
   ReturnUrl
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

    Developer API key (Deprecated) [Http]

   Deprecated authentication method. Use [Private app] instead. [API reference]

   Steps how to get and use Hubspot credentials
   

   Using HubSpot API key is deprecated method for authentication. Use Private app authentication instead. If you still need one, then follow these instructions to get HubSpot API key:

   • First of all, if you don't have HubSpot Developer's account, register one.
   • Go to HubSpot and select your Developer account.
   • Click Apps on the left hand side and then click Get HubSpot API key button to get one:
   • When a popup appears, click Show Key button and then hit Copy button to copy API key:
   • Once copy the API key, go to SSIS package or ODBC data source and use it in API Key authentication configuration:
     • Paste it in ApiKey parameter field.
   • Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Developer API key (Deprecated) [Http]

   https://api.hubapi.com

   Required Parameters
   ApiKey	Fill-in the parameter...
   Optional Parameters
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

9. Select the desired endpoint, change/pass the properties values, and click on Preview Data button to make the API call.

   API Source - Hubspot

   Hubspot Connector can be used to integrate Hubspot and your data source, e.g. Microsoft SQL Server, Oracle, Excel, Power BI, etc. Get, write, delete Hubspot data in a few clicks!

   

10. That's it! We are done! Just in a few clicks we configured the call to Hubspot using Hubspot Connector.

    You can load the source data into your desired destination using the Upsert Destination, which supports SQL Server, PostgreSQL, and Amazon Redshift. We also offer other destinations such as CSV, Excel, Azure Table, Salesforce, and more. You can check out our SSIS PowerPack Tasks and components for more options. (*loaded in Trash Destination)

    

Write data to Hubspot using SSIS (Import data)

Step-by-step instructions

In upper section we learned how to read data, now in this section we will learn how to configure Hubspot in the API Source to POST data to the Hubspot.

1. Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.

2. From the SSIS Toolbox drag and drop API Destination (Predefined Templates) on the Data Flow Designer surface and connect source component with it, and double click to edit it.
   

3. Select New Connection to create a new connection:
   

   API Destination - Hubspot

   Hubspot Connector can be used to integrate Hubspot and your data source, e.g. Microsoft SQL Server, Oracle, Excel, Power BI, etc. Get, write, delete Hubspot data in a few clicks!

   

4. Use a preinstalled Hubspot Connector from Popular Connector List or press Search Online radio button to download Hubspot Connector. Once downloaded simply use it in the configuration:
   

   Hubspot

   

5. Proceed with selecting the desired Authentication Type. Then select API Base URL (in most cases default one is the right one). Finally, fill in all the required parameters and set optional parameters if needed. You may press a link Steps to Configure which will help set certain parameters. More info is available in Authentication section.

    Private app (Static Access Token) [Http]

   Private apps allow you to use HubSpot's APIs to access specific data from your HubSpot account. You can authorize what each private app can request or change in your account, which will generate an access token that's unique to your app. [API reference]

   Steps how to get and use Hubspot credentials
   

   Follow these instructions to create a Private app in HubSpot and configure HubSpot authentication in SSIS package or ODBC data source:

   1. First of all, go to HubSpot portal.
   2. Then click Data Management option on the left and then hit Integrations menu item:
   3. Move on by clicking Private Apps menu item and then click Create a private app button to create one:
   4. Then continue by following the instructions on HubSpot Docs.
   5. Once you create an app, go to SSIS package or ODBC data source and in Private App authentication set these parameters:
      • For AccessToken field use Access token value from the Private app configuration in HubSpot.
   6. Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Private app (Static Access Token) [Http]

   https://api.hubapi.com

   Required Parameters
   AccessToken	Fill-in the parameter...
   Optional Parameters
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

    Public app [OAuth]

   In HubSpot, a public app is a type of integration that can be installed on customer accounts or listed on the App Marketplace. It requires authentication via OAuth. Once a user installs your app on their HubSpot account, you’ll be able to make API calls to that account using an OAuth access token. [API reference]

   Steps how to get and use Hubspot credentials
   

   Follow these instructions to create a Public app in HubSpot and configure HubSpot authentication in SSIS package or ODBC data source:

   1. First of all, if you don't have HubSpot Developer's account, register one.
   2. Go to HubSpot and select your Developer account.
   3. Click Apps on the left hand side and then click Create app button to create a Public app:
   4. Then continue by following the instructions in HubSpot Docs.
      NOTE: If you don't have a working Redirect URL, you can use https://zappysys.com/oauth (it's safe).

   5. Once you create an app, go to SSIS package or ODBC data source and in Public app authentication set these parameters (use values from your Public app configuration in HubSpot):
      • For ClientId field use Client ID value.
      • For ClientSecret field use Client secret value.
      • For Scope field use Scopes values, e.g.:

        accounting
        tickets
        files
        
        crm.lists.read
        crm.lists.write
        
        crm.objects.contacts.read
        crm.objects.contacts.write
        crm.schemas.contacts.read
        
        crm.objects.custom.read
        crm.objects.custom.write
        crm.schemas.custom.read
        
        crm.objects.companies.read
        crm.objects.companies.write
        crm.schemas.companies.read
        
        crm.objects.deals.read
        crm.objects.deals.write
        crm.schemas.deals.read
        
        crm.objects.quotes.read
        crm.objects.quotes.write
        crm.schemas.quotes.read
        
        crm.objects.line_items.read
        crm.objects.line_items.write
        crm.schemas.line_items.read
        

        NOTE: You can use other scopes too, but they must match the scopes defined in your Public app.

      • For ReturnUrl field use one of Redirect URLs.
   6. When you are done with that, click Generate Token button to generate Access and Refresh Tokens.
   7. Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Public app [OAuth]

   https://api.hubapi.com

   Required Parameters
   UseCustomApp	Fill-in the parameter...
   Scope (Must Match with App Scopes)	Fill-in the parameter...
   Optional Parameters
   Client ID
   Client Secret
   ReturnUrl
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

    Developer API key (Deprecated) [Http]

   Deprecated authentication method. Use [Private app] instead. [API reference]

   Steps how to get and use Hubspot credentials
   

   Using HubSpot API key is deprecated method for authentication. Use Private app authentication instead. If you still need one, then follow these instructions to get HubSpot API key:

   • First of all, if you don't have HubSpot Developer's account, register one.
   • Go to HubSpot and select your Developer account.
   • Click Apps on the left hand side and then click Get HubSpot API key button to get one:
   • When a popup appears, click Show Key button and then hit Copy button to copy API key:
   • Once copy the API key, go to SSIS package or ODBC data source and use it in API Key authentication configuration:
     • Paste it in ApiKey parameter field.
   • Done! Now you are ready to use HubSpot Connector!

   
   Configuring authentication parameters
   

   Hubspot

   Developer API key (Deprecated) [Http]

   https://api.hubapi.com

   Required Parameters
   ApiKey	Fill-in the parameter...
   Optional Parameters
   RetryMode	RetryWhenStatusCodeMatch
   RetryStatusCodeList	429
   RetryCountMax	5
   RetryMultiplyWaitTime	True

   

6. Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.

   API Destination - Hubspot

   Hubspot Connector can be used to integrate Hubspot and your data source, e.g. Microsoft SQL Server, Oracle, Excel, Power BI, etc. Get, write, delete Hubspot data in a few clicks!

   

7. Finally, map the desired columns:

   API Destination - Hubspot

   Hubspot Connector can be used to integrate Hubspot and your data source, e.g. Microsoft SQL Server, Oracle, Excel, Power BI, etc. Get, write, delete Hubspot data in a few clicks!

   

8. That's it; we successfully configured the POST API Call. In a few clicks we configured the Hubspot API call using ZappySys Hubspot Connector

   

Load Hubspot data into SQL Server using Upsert Destination (Insert or Update)

Once you configured the data source, you can load Hubspot data into SQL Server using Upsert Destination.

Upsert Destination can merge or synchronize source data with the target table. It supports Microsoft SQL Server, PostgreSQL, and Redshift databases as targets. Upsert Destination also supports very fast bulk upsert operation along with bulk delete.

1. From the SSIS Toolbox drag-and-drop Upsert Destination component onto the Data Flow designer background.

2. Connect your SSIS source component to Upsert Destination.

3. Double-click on Upsert Destination component to open configuration window.

4. Start by selecting the Action from the list.

5. Next, select the desired target connection or create one by clicking <New [provider] Connection> menu item from the Target Connection dropdown.

6. Then select a table from the Target Table list or click New button to create a new table based on the source columns.

7. Continue by checking Insert and Update options according to your scenario (e.g. if Update option is unchecked, no updates will be made).

8. Finally, click Map All button to map all columns and then select the Key columns to match the columns on:

   

9. Click OK to save the configuration.

10. Run the package and Hubspot data will be merged with the target table in SQL Server, PostgreSQL, or Redshift:

    

11. Done!

Deploy and schedule SSIS package

After you are done creating SSIS package, most likely, you want to deploy it to SQL Server Catalog and run it periodically. Just follow the instructions in this article:

• How to design, debug, deploy, schedule SSIS Package (In SQL Agent and Catalog)

Running SSIS package in Azure Data Factory (ADF)

To use SSIS PowerPack in ADF, you must first prepare Azure-SSIS Integration Runtime. Follow this link for detailed instructions:

• Preparing ADF instance to use SSIS PowerPack

Actions supported by Hubspot Connector

Hubspot Connector supports following actions for REST API integration:

Conclusion

In this article we showed you how to connect to Hubspot in SSIS and integrate data without any coding, saving you time and effort.

We encourage you to download Hubspot Connector for SSIS and see how easy it is to use it for yourself or your team.

If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.

Download Hubspot Connector for SSIS Documentation

More integrations

Other connectors for SSIS

Amazon Ads

Amazon Athena

Amazon Selling Partner (SP-API)

Apache Derby

Apache Hive

Apache Spark

Asana

Azure DevOps

Cosmos DB

DropBox

ElasticSearch

FastSpring

Google Ads

Google BigQuery

Google Calendar

Google Drive

Google Sheets

Infor Data Lake

Jira

Mailchimp

ManagedEngine ServiceDesk Plus (Zoho)

MariaDB

OData

OneDrive

Outlook Mail (Office 365)

Power BI

ServiceNow

SharePoint Online

Shopify

Smartsheet

Starburst

Stripe

Trino

YouTube

Zendesk

Zoho CRM

Zoho SalesIQ

Zoom

REST API

JSON

JSON File

XML

SOAP

XML File

JDBC-ODBC Bridge

Salesforce

CSV

Amazon S3 JSON File

Amazon S3 XML File

Amazon S3 CSV File

Azure Blob JSON File

Azure Blob XML File

Azure Blob CSV File

FTP/SFTP JSON File

FTP/SFTP XML File

FTP/SFTP CSV File

Google Analytics

HTML Table

Build your own Custom API Connector

Other application integration scenarios for Hubspot

SQL Server

Power BI

SSRS

Informatica

MS Access

MS Excel

SSAS

C#

Python

JAVA

Tableau

SAP Crystal Reports

Azure Data Factory (Pipeline)

Azure Data Factory (SSIS)

Talend Studio

UiPath

PowerShell

ODBC