Real-Time Integration: HubSpot to Dynamics CE/CRM

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.

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.

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.

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.

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:

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:

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.