Hubspot Connector
Documentation
Version: 5
Documentation

Write data to Hubspot using SSIS (Import data)


In this section we will learn how to configure and use Hubspot Connector in the API Destination to write data to Hubspot.

Video tutorial

This video covers following and more so watch carefully. After watching this video follow the steps described in this article.

  • How to download SSIS PowerPack for Hubspot integration in SSIS
  • How to configure connection for Hubspot
  • How to write or lookup data to Hubspot
  • Features about SSIS API Destination
  • Using Hubspot Connector in SSIS

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.
    SSIS API Destination (Predefined Templates) - Drag and Drop

  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!
    API Destination - New Connection

  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
    Hubspot Connector Selection

  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 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: Go to integrations in HubSpot
    3. Move on by clicking Private Apps menu item and then click Create a private app button to create one: Create private app to get HubSpot data via REST API
    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!

    Fill in all required parameters and set optional parameters if needed:

    HubspotDSN
    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
    ODBC DSN HTTP Connection Configuration

    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: Create public app to get HubSpot data via REST API
    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!

    Fill in all required parameters and set optional parameters if needed:

    HubspotDSN
    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
    ODBC DSN Oauth Connection Configuration

    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: Get HubSpot API key
    • When a popup appears, click Show Key button and then hit Copy button to copy API key: Use API key to read HubSpot data via REST API
    • 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!

    Fill in all required parameters and set optional parameters if needed:

    HubspotDSN
    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
    ODBC DSN HTTP Connection Configuration

  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!
    API Destination - Select Endpoint

  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!
    API Destination - Columns Mapping

  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

    Execute Package - Reading data from API Source and load into target