Real-Time Integration: HubSpot to Dynamics CE/CRM

23 June 2020
Chen Huang

We recently launch our new product, the KingswaySoft Integration Gateway, which offers real-time integration capabilities using webhooks. Webhook is a web callback URL that is generally triggered by an event or action in the system and delivers data to the target when it happens. In contrast to batch integration which usually involves querying the source data with a filter condition to detect  new/changed records, webhook provides you with the new/changed records whenever such events happen in the source system.

In this blog post, we will demonstrate how to set up a webhook listener in KingswaySoft Integration Gateway and design an SSIS package with our SSIS Productivity Pack components that will be triggered when Integration Gateway receives an inbound webhook message from your online services. Specifically, we will use HubSpot and Dynamics 365 CE/CRM as our source system and target system respectively, and set up a trigger where an SSIS package writes HubSpot data to Dynamics 365 CRM when a new contact is created in HubSpot.

Step 1: Obtain HubSpot Webhook URL

Before we get started to set up the HubSpot webhook, we need to set and configure the webhook URL. KingswaySoft Integration Gateway works like an HTTP server that listens to incoming webhook calls. Simply install Integration Gateway on your integration server, and once installed, run the Integration Gateway Console. The HTTP Server tab is where you will need to configure the HTTP server settings.

KingswaySoft Integration Gateway Console - HTTP Server page

As shown in the screenshot above, the Base URL of our Integration Gateway listener will be: https://<Server>:443/

Step 2: Register the Webhook in HubSpot Workflow

Our next step is to register the HubSpot webhook by registering the URL to notify once given events occur.

HubSpot offers two ways of using webhook, one is the Webhook API which is used to send out notifications when given events happen. The webhook payload contains the subscriptionType and ID of the object that was created/changed/deleted (objectid). However, the object data is not included in the webhook payload under the Webhook API. An alternative method of using webhooks in HubSpot is webhooks in workflow approach. It can be used to post HubSpot data to another system when a record matches a certain condition in HubSpot.

For our requirements, we will use the HubSpot workflow approach with webhook triggers to get new contacts in HubSpot. To easily identify and track new contacts and synced contacts in HubSpot, we will create a string type custom field named “crm_contactid” in HubSpot Contact object, which will be used to store the Dynamics 365 CRM contactid value in HubSpot.

After the “crm_contactid” custom field is created, we will use this field in the HubSpot workflow filter criteria. Here's how to set up the HubSpot workflow:

  1. In your HubSpot account, navigate to Automation > Workflows.
  2. Create a new contact-based workflow with criteria: crm_contactid is unknown.
  3. Click the + icon to add an action and in the right panel, select Trigger a webhook. From the drop-down list, choose POST. Enter the webhook URL “https://<Server>:<port>/<route>” and save. The webhook URL is the URL we got from Step 1 within the Integration Gateway HTTP Server settings.

HubSpot Workflow with Webhook

Step 3: Configure Inbound Webhook

After the webhook is registered in HubSpot, we can now configure the Integration Gateway settings to listen to the HTTP webhook traffic and choose the destination to temporarily store the webhook messages.

In Integration Gateway Inbound Webhooks tab, add a Custom webhook and set the Route to “hubspot”, by following the webhook URL we used in HubSpot.

Integration Gateway - Inbound Webhook

For our scenario, we are using Azure Service Bus as our temporary destination to store the HubSpot webhook messages.

Simply click the ‘Start’ button in Integration Gateway to allow the application to run as a Windows Service to constantly listen to the webhook messages and push the messages to the Azure Service Bus message queue.

Step 4: Implement SSIS Package Triggered by Webhook Messenger

With the Integration Gateway set up and running, it’s time to create an SSIS package that would pick up the messages and perform real-time integration.

We will be using the Premium Message Queue Task component offered in our SSIS Productivity Pack, which listens for changes in the queue, and then invokes a data flow task which would process the message whenever a message arrives at the queue.

Premium Message Queue Task Setting

As shown in the screenshot above, we are listening to the Azure Service Bus queue and writing the message to an SSIS variable. The listener mode is set to “Until Service is Stopped”, which allows the component to exit the listener mode till a message occurs.

Our next step is to link a Data Flow Task to the Premium Message Queue to write the HubSpot contact record to the target Dynamics 365 CE/CRM system. As the HubSpot webhook payload is JSON formatted, we will use the JSON Source component to read from the SSIS variable and parse the JSON document into columns and rows.

The data flow would look something like this:

HubSpot Contact to CRM Data Flow

As Premium Message Queue Task works with one message at a time, we would need to use a For Loop Container in the SSIS package, so the package design would look like this:

For Loop Container Package Design

We create two datetime type variables in SSIS:

  • User::CurrentDateTime: GETDATE()
  • User::EndTime: (DT_DBTIMESTAMP) ((DT_WSTR,20)(DT_DBDATE)GETDATE()+ " 11:59:59 PM")

These two variables would be used in For Loop EvalExpression to specify when the loop stops.

  • @[User::CurrentDateTime]<@[User::EndTime]

In our sample package, we have set the For Loop to stop daily at 11:59:59 PM. Then we can simply deploy the package and run the package on schedule every day at 12:00:00 AM so that the SSIS package is always listening for the message queue messages.

Sample SSIS Package

A sample SSIS package can be downloaded here.

The sample package was prepared in an SQL Server 2012 format, and it should automatically upgrade if you are using any newer version of SSIS.

Closing Notes

Integration Gateway creates numerous opportunities to bring real-time integration to your ETL projects as we support custom webhooks in addition to the built-in webhooks available with the solution. You can work with any webhook-enabled modern cloud application to bring real-time integration to your business. Interested in more information about our Integration Gateway product? Check out this portion of our 2020 Release Wave 1 webinar on YouTube.

Archive

November 2024 3 October 2024 1 September 2024 1 August 2024 2 July 2024 1 June 2024 1 May 2024 1 April 2024 2 March 2024 2 February 2024 2 January 2024 2 December 2023 1 November 2023 1 October 2023 2 August 2023 1 July 2023 2 June 2023 1 May 2023 2 April 2023 1 March 2023 1 February 2023 1 January 2023 2 December 2022 1 November 2022 2 October 2022 2 September 2022 2 August 2022 2 July 2022 3 June 2022 2 May 2022 2 April 2022 3 March 2022 2 February 2022 1 January 2022 2 December 2021 1 October 2021 1 September 2021 2 August 2021 2 July 2021 2 June 2021 1 May 2021 1 April 2021 2 March 2021 2 February 2021 2 January 2021 2 December 2020 2 November 2020 4 October 2020 1 September 2020 3 August 2020 2 July 2020 1 June 2020 2 May 2020 1 April 2020 1 March 2020 1 February 2020 1 January 2020 1 December 2019 1 November 2019 1 October 2019 1 May 2019 1 February 2019 1 December 2018 2 November 2018 1 October 2018 4 September 2018 1 August 2018 1 July 2018 1 June 2018 3 April 2018 3 March 2018 3 February 2018 3 January 2018 2 December 2017 1 April 2017 1 March 2017 7 December 2016 1 November 2016 2 October 2016 1 September 2016 4 August 2016 1 June 2016 1 May 2016 3 April 2016 1 August 2015 1 April 2015 10 August 2014 1 July 2014 1 June 2014 2 May 2014 2 February 2014 1 January 2014 2 October 2013 1 September 2013 2 August 2013 2 June 2013 5 May 2013 2 March 2013 1 February 2013 1 January 2013 1 December 2012 2 November 2012 2 September 2012 2 July 2012 1 May 2012 3 April 2012 2 March 2012 2 January 2012 1

Tags