How to Connect Hubspot to SQL Server

We’re entering a new world in which data may be more important than software.
Tim O’Reilly, founder, O’Reilly Media

Businesses are hunting for the digital gold of nowadays – DATA that usually comes from heterogeneous sources in various formats. It’s like different kinds of fruit come from multiple trees. And retrieving data from various sources is like extracting juice from fruit – a reliable instrument is needed for that.

Nowadays, companies implement different tools, apps, and databases, where HubSpot and SQL Server are among the most popular ones. The question is – how to get a single source of truth while using such different types of software? The solution is obvious – HubSpot and SQL integration would be appropriate to analyze data and derive precise conclusions from it.

In this article, we focus on the methods to set up HubSpot SQL Server integration to obtain a single source of truth with step-by-step guidelines. We also explain how companies benefit from HubSpot connection to SQL database based on real business cases.

Table of Contents

1. What is Hubspot?
   • The Benefits of Hubspot
2. SQL Server for HubSpot Integration Overview
3. Cloud Data Integration HubSpot and SQL Server
   • Business Case 1. Сreating a Single Source of Truth
   • Business case  2. Making Data Consistent for Strategic Planning and Forecasting
   • Advantages of Skyvia
4. ODBC Connection HubSpot to SQL Server
5. Conclusion

What Is Hubspot?

HubSpot is a powerful CRM platform that perfectly suits marketing, sales, and customer service teams. Each of HubSpot’s components is designed to satisfy the needs of those departments:

• Marketing Hub: It conceals marketing automation potential and handles email marketing.
• Sales Hub: It analyzes deals in the free version and has a sales forecasting function under the Professional (Premium) plan.
• Service Hub: It coordinates interaction with customers through tickets and offers feedback survey analysis and knowledge base elaboration as extra options.

The Benefits of Hubspot

The principal reason for HubSpot being the most popular tool is that it’s free to use. Extra payment is required only for additional features. Another thing is that HubSpot brings the following advantages and values to companies:

• Marketing automation. This tool provides mechanisms to design strategies based on the target audience’s interest and customize marketing approaches with low effort.
• Customer behaviors overview. HubSpot gathers data on customer engagement and their interactions with the brand. Thus, businesses better understand the ‘buyer persona’ profile, customer lifecycle, average deal amount, and other parameters highlighting performance overview.
• Scaling. This platform supports any kind of business, ranging from SMBs to large corporations, to ensure effective marketing.
• Reporting. HubSpot provides reporting and analytics solutions, giving insights into the performance of lead generation, sales, email campaigns, and other marketing activities.

Despite all those advantages, HubSpot doesn’t provide a holistic overview of business performance, so companies rely on other tools in the meantime. Read how Salesforce connects with HubSpot and integrates with MS Dynamics 365 for a better understanding of the customer profile. Here we focus on HubSpot SQL integration and how it influences business performance indicators tracking and forecasting.

SQL Server for HubSpot Integration Overview

When referring to SQL Server to HubSpot integration, the task of defining key metrics for a business, such as analysis of sales or financial metrics, comes into mind. For instance, if the company’s active sales are tracked in HubSpot, and passive ones are made on the website based on SQL Server, it would be difficult to analyze them properly within two different sources. As a solution, it’s necessary to make either HubSpot or SQL Server a single source of truth to gather entire sales data in one place.

Another aspect where HubSpot and SQL Server integration is applicable touches upon forecasting of sales, production, and logistic operations for companies. This requires the gathering of considerable data amounts and further processing with ML and AI algorithms. Companies have to collect and store data from heterogenous sources and take care of its quality for precise predictions. As data ranges in format, category, structure, etc., it’s necessary to make it consistent through complex transformations.

Based on the cases described above, let’s see how companies can integrate HubSpot with SQL Sever to resolve them. In this article, we focus on two methods for that: using Skyvia and the ODBC driver from Devart.

Cloud Data Integration HubSpot and SQL Server

Now let’s dive into the detailed description of how Skyvia integrates Hubspot and SQL Server data based on various business cases.

Skyvia is a universal SaaS (Software as a Service) data platform designed for a wide set of data-related tasks without coding. This service is hosted online and requires no locally installed software except for a web browser.

This platform is flexible enough to support a wide range of integration scenarios for HubSpot and SQL Server integration:

• Import packages load data from HubSpot to SQL Server or vice versa, using data transformations via powerful mapping settings.   The Import tool of Skyvia complies with ETL and reverse ETL scenarios.
• Replication packages can create a copy of HubSpot data on SQL Server data and keep it up-to-date automatically. The Replication tool of Skyvia complies with the ELT scenario.
• Synchronization packages are for one- or bi-directional synchronization between two data sources – HubSpot and SQL Server. The Synchronization tool is compatible with a bi-directional synchronization integration scenario between tools. Note that one of the data sources should preferably be empty to avoid data duplicates.
• Data Flow packages allow the creation of complex integration involving more than two data sources and advanced data mapping and multistage transformations. Together with Control Flow packages, which create logic and run data flows according to specified conditions, it’s possible to create compound ETL data pipelines.

Now let’s have a look at particular examples of how to carry out HubSpot and SQL Server integration using Skyvia.

NOTE: Regardless of the business case of interest, the following preparation steps are necessary:

1. Establish connection with SQL Server in Skyvia (if not added yet). See detailed instructions here.
2. Establish connection with HubSpot in Skyvia (if not added yet). See detailed instructions here.

Business Case 1. Сreating a Single Source of Truth

When there’s a need to review key metrics of business – sales metrics in this case – the data is gathered from several sources first. Active sales are registered in HubSpot CRM, while passive sales records from the online shop can be kept in SQL Server. Analyzing data in each tool separately won’t bring a quality result. Therefore, it’s necessary to create a single source of truth where all the sales data would be integrated.

In this case, HubSpot CRM becomes a single point of truth guaranteeing the enhancement of the sales management process. It also offers ready-made analytics that can be used by marketing and sales departments.

See the instructions on how to transfer information about deals from SQL Server for passive sales from the online shop to HubSpot by creating the import package in Skyvia:

1. Click +NEW in the top menu.
2. In the Integration column, click Import. The package editor opens.
3. Under Source Type, click Data Source and select SQL Server from the Connection drop-down list.
4. Under Target, select HubSpot from the Connection drop-down list.

5. Click Add New to open the Task Editor settings window.
6. In the Task Editor window, select Deals from the Source drop-down list.
7. Add desired filters. For example, you can import only those deals whose value is above a certain number. For this, click +Condition to set a specific condition.

8. Click Next Step.
9. In the Target drop-down list, select Deals.
10. Click Next step.
11. On the Mapping Definition tab, check whether all required columns are mapped.
12. Click Schedule to set the timing of the package.

13. Click Save.
14. Click Create in the tab bar to preserve the import package.
15. Click Save to save the task.

To execute the import package immediately, click Run. Otherwise, it’s possible to transfer data from HubSpot to Snowflake later — the import package could be found under Objects -> Integrations.

Establishing a single source of truth isn’t only suitable for sales analysis. It conceals other benefits for teams across the company:

• Marketing teams communicate with clients in a more efficient manner by having exhaustive contact and behavior details at a one-stop shop.
• Sales teams get an influx of lead contacts from another data source and thus can elaborate on conversion rates.
• Service teams can effectively execute customer care and support programs by having all customer-related data in one place.

Business case  2. Making Data Consistent for Strategic Planning and Forecasting

Companies are no longer satisfied with current performance analysis but aim to predict future sales, production, logistics, etc. As a result, there is a need to gather huge amounts of data from several sources and process them with ML/AI algorithms for prognosis and forecasting.

One of the possible realizations to do that is to transfer data from HubSpot to SQL Server that already stores data from other sources as well. Then connect solutions for ML/AI to SQL Server for further strategy evolvement and prognosis.

To set everything up, create the replication package with Skyvia so the system moves HubSpot data to SQL Server by simply creating an exact data copy without mapping configuration and maintaining it up-to-date automatically with incremental updates.

To configure the replication package, follow these instructions:

1. Click +NEW in the top menu.
2. In the Integration column, click Replication. The package editor opens.
3. Under Source Type, click Data Source and select HubSpot from the Connection drop-down list.
4. Under Target, select SQL Server from the Connection drop-down list.
5. Select the necessary checkboxes under Options:
   • Incremental Updates
   • Create Tables
   • Drop Tables
   • Create Foreign Keys

6. Select objects for replication.

7. Optionally schedule the package for automatic execution.

Note: Skyvia also connects HubSpot with NetSuite and other 180+ data sources and applications.

Advantages of Skyvia

Performing SQL Server and HubSpot integration with Skyvia is convenient and fast experience. Skyvia also has hidden gems – so let’s discover them together:

• Multifunctionality. Along with the scenarios we have already explored, Skyvia offers solutions for multiple data-related tasks. Synchronization, replication, cloud backup, and process automation are among other popular solutions customers can benefit from.
• Easy-to-use. Having an intuitive user interface, Skyvia is comfortable to use and navigate.
• Price. Skyvia offers a Free plan to start, and its flexible paid plans suit businesses of any size, from small startups to enterprise companies.
• Security. Ability to securely share data in a governed way internally across the organization and externally with partners, vendors, and customers without moving or copying the data.

ODBC Connection HubSpot to SQL Server 

Another method to bind HubSpot and SQL Server implies the installation of the Devart ODBC driver. It enables ODBC-aware applications such as SQL Server to connect to HubSpot directly via HTTPS. Moreover, it ensures full support for HubSpot field data types and is fully compatible with HubSpot API.

Before launching data transfer from HubSpot to the SQL server, download the ODBC driver for HubSpot and control the following:

• The driver, SQL Server, and management studio application must all have the same bitness (32-bit or 64-bit).
• The ODBC Driver for HubSpot and SQL Server must be installed on the same computer.
• .NET Framework 4.5+ must be installed.

SQL Server Management Studio has the Linked Server option to refer to non-SQL Server tables by executing distributed queries. That way, it’s easy to connect HubSpot data to SQL Server instances.

Let’s have a look at the following configuration steps:

1. Launch Management Studio and choose your SQL Server instance.
2. In the Object Explorer pane, expand the Server Objects. Then right-click Linked Servers and select New Linked Server.

3. Type the name of the server in the Linked server field.
4. Under Server type, select Other data source.
5. Choose Microsoft OLE DB Provider for ODBC Drivers in the Provider drop-down list.
6. In the Data source field, type the name of your DSN ( e.g. Devart ODBC Driver for HubSpot). Alternatively, you can input the ODBC Driver connection string in the Provider field.

-> The linked server now appears under the Linked Servers list in the Object Explorer Pane.

7. Find the MSDASQL provider in the list of Linked Servers and double-click on it.

8. Select the Allow inprocess checkbox in the Provider Options window.
9. Right-click Linked Servers and select New Linked Server.

10. Select Microsoft OLE DB Provider for ODBC Drivers from the Provider drop-down list.

11. Type the name of the server in the Linked server field (e.g. HUBSPOT).
12. In the Product Name and Data Source fields, indicate a previously created System DSN.
13. Under Server type, select Other data source.

14. Click New Query in the toolbar.
15. Retrieve data from the HubSpot tables by typing an SQL query in the editor window. Click Execute to run the query.

Now the contents of the retrieved HubSpot table appear.

Even though the ODBC driver method allows connecting to non-SQL apps and retrieving data from them, it creates some difficulties. As the difference between data types takes place in SQL and non-SQL systems, conversion at the Driver Manager level takes place, which results in extra software overhead. 

Conclusion

Data integration between tools is necessary for many reasons: whether a company wants to track key performance metrics or pack all the customer information into a corporate CRM.

To benefit from data integration between HubSpot and SQL Server:

• Use Skyvia to integrate HubSpot and SQL data to define key business metrics and perform complex forecasting and strategy development to predict the company’s future.
• Install ODBC driver for HubSpot by creating a linked server to ingest data from non-SQL apps.