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.
-
Read the data from the source, being any desired source component. In example we will use ZappySys Dummy Data Source component.
-
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.
-
Select New Connection to create a new connection:
API Destination - HubspotHubspot 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! -
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 -
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:
- First of all, go to HubSpot portal.
-
Then click Data Management option on the left
and then hit Integrations menu item:
-
Move on by clicking Private Apps menu item
and then click Create a private app button to create one:
- Then continue by following the instructions on HubSpot Docs.
-
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.
- Done! Now you are ready to use HubSpot Connector!
Fill in all required parameters and set optional parameters if needed:
HubspotDSNHubspotPrivate app (Static Access Token) [Http]https://api.hubapi.comRequired Parameters AccessToken Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True 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:
- 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 Create app button to create a Public app:
-
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).- 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.
- When you are done with that, click Generate Token button to generate Access and Refresh Tokens.
- Done! Now you are ready to use HubSpot Connector!
Fill in all required parameters and set optional parameters if needed:
HubspotDSNHubspotPublic app [OAuth]https://api.hubapi.comRequired 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
- 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):
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:
HubspotDSNHubspotDeveloper API key (Deprecated) [Http]https://api.hubapi.comRequired Parameters ApiKey Fill-in the parameter... Optional Parameters RetryMode RetryWhenStatusCodeMatch RetryStatusCodeList 429 RetryCountMax 5 RetryMultiplyWaitTime True Select the desired endpoint, change/pass the properties values, and go to the Mappings tab to map the columns.
API Destination - HubspotHubspot 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!Finally, map the desired columns:
API Destination - HubspotHubspot 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!That's it; we successfully configured the POST API Call. In a few clicks we configured the Hubspot API call using ZappySys Hubspot Connector