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.
   

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!

   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

   

    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!

   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

   

    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!

   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

   

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