Hubspot Connector - Using Stored Procs

T-SQL is a programming language used for managing and querying data in Microsoft SQL Server databases. We provides tools for data integration, automation, and connectivity.

If you want to use T-SQL with ZappySys, you can use our Connector, which allows you to connect to SQL Server databases and execute T-SQL queries from Hubspot.

Create Data Source in ZappySys Data Gateway based on API Driver

Download and install ZappySys ODBC PowerPack.
Search for gateway in start menu and Open ZappySys Data Gateway:
Go to Users Tab to add our first Gateway user. Click Add; we will give it a name tdsuser and enter password you like to give. Check Admin option and click OK to save. We will use these details later when we create linked server:
Now we are ready to add a data source. Click Add, give data source a name (Copy this name somewhere, we will need it later) and then select Native - ZappySys API Driver. Finally, click OK. And it will create the Data Set for it and open the ZS driver UI.

HubspotDSN
When the Configuration window appears give your data source a name if you haven't done that already, then select "Hubspot" from the list of Popular Connectors. If "Hubspot" is not present in the list, then click "Search Online" and download it. Then set the path to the location where you downloaded it. Finally, click Continue >> to proceed with configuring the DSN:

HubspotDSN

Hubspot

Now it's time to configure the Connection Manager. Select Authentication Type, e.g. Token Authentication. Then select API Base URL (in most cases, the default one is the right one). More info is available in the 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:

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:

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:

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:

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

Once the data source has been configured, you can preview data. Select the Preview tab and use settings similar to the following to preview data:
Click OK to finish creating the data source.

Read data in SQL Server from the ZappySys Data Gateway

To read the data in SQL Server the first thing you have to do is create a Linked Server. Go to SQL Server Management Studio and configure it in a similar way:
Then click on Security option and configure username we created in ZappySys Data Gateway in one of the previous steps:
Optional: Under the Server Options, Enable RPC and RPC Out and Disable Promotion of Distributed Transactions(MSDTC).

You need to enable RPC Out if you plan to use EXEC(...) AT [MY_LINKED_SERVER_NAME] rather than OPENQUERY.
If don't enabled it, you will encounter the Server 'MY_LINKED_SERVER_NAME' is not configured for RPC error.

Query Example:
```
EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]
```
If you plan to use 'INSERT INTO...EXEC(....) AT [MY_LINKED_SERVER_NAME]' in that case you need to Disable Promotion of Distributed Transactions(MSDTC).
If don't disabled it, you will encounter the The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "MY_LINKED_SERVER_NAME" was unable to begin a distributed transaction. error.

Query Example:
```
Insert Into dbo.Products
 EXEC('Select * from Products') AT [MY_LINKED_SERVER_NAME]
```
Finally, open a new query and execute a query we saved in one of the previous steps:
```
SELECT * FROM OPENQUERY([MY_LINKED_SERVER_NAME], 'SELECT * FROM Products');
```

Create Linked Server using Code

In previous section you saw how to create a Linked Server from UI. You can do similar action by code too (see below). Run below script after changing necessary parameters. Assuming your Data Source name on ZappySys Data Gateway UI is 'HubspotDSN'


    USE [master]
    GO
    --///////////////////////////////////////////////////////////////////////////////////////
    --Run below code in SSMS to create Linked Server and use ZappySys Drivers in SQL Server
    --///////////////////////////////////////////////////////////////////////////////////////

    //Replace YOUR_GATEWAY_USER, YOUR_GATEWAY_PASSWORD
    //Replace localhost with IP/Machine name if ZappySys Gateway Running on different machine other than SQL Server
    //Replace Port 5000 if you configured gateway on a different port


    --1. Configure your gateway service as per this article https://zappysys.com/links?id=10036

    --2. Make sure you have SQL Server Installed. You can download FREE SQL Server Express Edition from here if you dont want to buy Paid version https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

    --Uncomment below if you like to drop linked server if it already exists
    --EXEC master.dbo.sp_dropserver @server=N'LS_HubspotDSN', @droplogins='droplogins'

    --3. Create new linked server

    EXEC master.dbo.sp_addlinkedserver
      @server = N'LS_HubspotDSN'  --Linked server name (this will be used in OPENQUERY sql
    , @srvproduct=N''
    ---- For MSSQL 2012,2014,2016 and 2019 use below (SQL Server Native Client 11.0)---
    , @provider=N'SQLNCLI11'
    ---- For MSSQL 2022 or higher use below (Microsoft OLE DB Driver for SQL Server)---
    --, @provider=N'MSOLEDBSQL'
    , @datasrc=N'localhost,5000' --//Machine / Port where Gateway service is running
    , @provstr=N'Network Library=DBMSSOCN;'
    , @catalog=N'HubspotDSN' --Data source name you gave on Gateway service settings

    --4. Attach gateway login with linked server

    EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname=N'LS_HubspotDSN'  --linked server name
    , @useself=N'False'
    , @locallogin=NULL
    , @rmtuser=N'YOUR_GATEWAY_USER' --enter your Gateway user name
    , @rmtpassword='YOUR_GATEWAY_PASSWORD'  --enter your Gateway user's password
    GO

    --5. Enable RPC OUT (This is Optional - Only needed if you plan to use EXEC(...) AT YourLinkedServerName rather than OPENQUERY
    EXEC sp_serveroption 'LS_HubspotDSN', 'rpc', true;
    EXEC sp_serveroption 'LS_HubspotDSN', 'rpc out', true;

    --Disable MSDTC - Below needed to support INSERT INTO from EXEC AT statement
    EXEC sp_serveroption 'LS_HubspotDSN', 'remote proc transaction promotion', false;

    --Increase query timeout if query is going to take longer than 10 mins (Default timeout is 600 seconds)
    --EXEC sp_serveroption 'LS_HubspotDSN', 'query timeout', 1200;
    GO

Create Custom Stored Procedure in ZappySys Driver

You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Steps to create Custom Stored Procedure in ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here

Go to Custom Objects Tab and Click on Add button and Select Add Procedure:
Enter the desired Procedure name and click on OK:
Select the created Stored Procedure and write the your desired stored procedure and Save it and it will create the custom stored procedure in the ZappySys Driver:
Here is an example stored procedure for ZappySys Driver. You can insert Placeholders anywhere inside Procedure Body. Read more about placeholders here
```
CREATE PROCEDURE [usp_get_orders]
    @fromdate = '<<yyyy-MM-dd,FUN_TODAY>>'
 AS
    SELECT * FROM Orders where OrderDate >= '<@fromdate>';
```
That's it now go to Preview Tab and Execute your Stored Procedure using Exec Command. In this example it will extract the orders from the date 1996-01-01:
```
Exec usp_get_orders '1996-01-01';
```
Let's generate the SQL Server Query Code to make the API call using stored procedure. Go to Code Generator Tab, select language as SQL Server and click on Generate button the generate the code.
As we already created the linked server for this Data Source, in that you just need to copy the Select Query and need to use the linked server name which we have apply on the place of [MY_API_SERVICE] placeholder.
```
SELECT * FROM OPENQUERY([MY_API_SERVICE], 'EXEC usp_get_orders @fromdate=''1996-07-30''')
```
Now go to SQL served and execute that query and it will make the API call using stored procedure and provide you the response.