Modifying Hubspot Connector

There might be a case, when you want to customize Hubspot Connector. This can happen if your timeline does not allow waiting until the next software release from ZappySys, when:

• Hubspot Connector has a bug and you need to fix it promptly
• You need a new functionality right away
• You need to tweak existing Hubspot Connector functionality

If that's the case, all you have to do is:

1. Download Hubspot Connector
2. Modify connector's XML configuration according to your needs
3. Use it in an SSIS package or in an ODBC data source

Increasing column length

If you get an error that says The value is too large to fit in the column data area of the buffer or a similar one, you might consider increasing the length of the string data type column:

Original EndPoint

Suppose you have this Hello-World Connector EndPoint which calls Sandbox World API and returns a list of friends with their IDs and names. You recognize that 20 characters for the Name column could be too few:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">
    <OutputColumns>
        <Column Name="id" Label="Id" DataType="DT_R4" />
        <Column Name="name" Label="Name" DataType="DT_WSTR" Length="20" />   <!-- Only 20 symbols are allocated -->
    </OutputColumns>
<EndPoint>

Updated EndPoint

You simply increase the Name column Length to 200:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">
    <OutputColumns>
        <Column Name="id" Label="Id" DataType="DT_R4" />
        <Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />   <!-- We increase it to 200 symbols -->
    </OutputColumns>
<EndPoint>

Changing column data type

In certain cases, you need to update the data type of a column. It could happen because of the changes in data returned by the API or for other reasons. Consider this scenario:

Original EndPoint

Suppose you have this Hello-World Connector EndPoint which calls Sandbox World API and returns a list of friends with their IDs and names. You notice that the Id column data type DT_R4 used for real numbers is not the best one to denote an ID:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">
    <OutputColumns>
        <Column Name="id" Label="Id" DataType="DT_R4" />   <!-- DT_R4 data type is not the best one for natural numbers -->
        <Column Name="name" Label="Name" DataType="DT_STR" Length="200" />   
    </OutputColumns>
<EndPoint>

Updated EndPoint

You simply update the Id column DataType to DT_I4:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">
    <OutputColumns>
        <Column Name="id" Label="Id" DataType="DT_I4" />   <!-- We update it to DT_I4 data type -->
        <Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />
    </OutputColumns>
<EndPoint>

Adding a column

If you need an additional field for reading in the EndPoint, which is not supported by Hubspot Connector, you can add a new column by mapping it to Hubspot API response field.

Mapping column to a field

Suppose you have this Hello-World Connector EndPoint which calls Sandbox World API and returns a list of friends with their IDs and names:

Original EndPoint

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">
    <OutputColumns>
        <Column Name="id" Label="Id" DataType="DT_I4" />
        <Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />
    </OutputColumns>
<EndPoint>

Data returned

But you know that the API response contains more information about each friend, e.g. their birthdate, how much money they have in their pocket, etc., which is not returned by the EndPoint:

API response

{  
    "totalFriends": 3,  
    "friends": [  
       {  
           "id": 1,
           "name": "Alice",  
           "birthdate": "2000-01-02",  
           "extras": {  
               "bornAt": "2000-01-02T12:23:45.0001Z",  
               "dollarsInPocket": 60.25,  
               "isAfraidOfSpiders": true  
           }  
       },  
       {  
           "id": 2,
           "name": "Bob",  
           "birthdate": "2001-02-03",  
           "extras": {  
               "bornAt": "2001-02-03T12:23:45.0001Z",  
               "dollarsInPocket": 120.50,  
               "isAfraidOfSpiders": false  
           }  
       },  
       {  
           "id": 3,
           "name": "Charlie",  
           "birthdate": "2002-03-04",  
           "extras": {  
               "bornAt": "2002-03-04T12:23:45.0001Z",  
               "dollarsInPocket": 180.75,  
               "isAfraidOfSpiders": true  
           }  
       }  
    ]  
}

Updated EndPoint

You can add all the additional fields you need to the EndPoint configuration:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">  
   <OutputColumns>  
       <Column Name="id" Label="Id" DataType="DT_I4" />  
       <Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />  
       <!-- Newly added columns below -->
       <Column Name="birthdate" Label="Birthdate" DataType="DT_DBDATE" />  
       <Column Name="extras.bornAt" Label="BornAt" DataType="DT_DBDATETIMESTAMP" />  
       <Column Name="extras.dollarsInPocket" Label="DollarsInPocket" DataType="DT_DECIMAL" />  
       <Column Name="extras.isAfraidOfSpiders" Label="IsAfraidOfSpiders" DataType="DT_BOOL" />  
   </OutputColumns>  
<EndPoint>

Mapping is done by using the Name attribute in the Column element. It acts like a path:

• JSON property birthdate is mapped by setting Name to birthdate
• JSON property bornAt, being a child of extras, is mapped by setting Name to extras.bornAt
• JSON property dollarsInPocket, being a child of extras, is mapped by setting Name to extras.dollarsInPocket
• JSON property isAfraidOfSpiders, being a child of extras, is mapped by setting Name to extras.isAfraidOfSpiders

Note how these four new columns have different data types:

• DT_DBDATE for date data type field for Birthdate column
• DT_DBDATETIMESTAMP for datetime data type field for BornAt column
• DT_DECIMAL for decimal data type field for DollarsInPocket column
• DT_BOOL for boolean data type field for IsAfraidOfSpiders column

Data returned after update

Mapping column to an array element

Sometimes a field is an array, and you want to get the first or nth element in it. Suppose, Sandbox World API updated /friends endpoint and added hobbies property in the response:

API response

{  
   "totalFriends": 3,  
   "friends": [  
       {  
           "id": 1,  
           "name": "Alice",  
           "birthdate": "2000-01-02",  
           "extras": {  
               "bornAt": "2000-01-02T12:23:45.0001Z",  
               "dollarsInPocket": 60.25,  
               "isAfraidOfSpiders": true,  
               "hobbies": [
                   {  
                       "name": "basketball",  
                       "yearsInterested": 15  
                   },  
                   {  
                       "name": "cards",  
                       "yearsInterested": 5  
                   }  
               ]  
           }  
       },
       ... // other friend objects not shown for brevity
   ]  
}

Updated EndPoint

To extract the first hobby from the hobbies array together with name and yearsInterested properties, you would have to add these two Columns:

<EndPoint Name="get_friends" Label="Get Friends" Url="/friends" Filter="$.friends[*]">  
   <OutputColumns>  
      <Column Name="id" Label="Id" DataType="DT_I4" />  
      <Column Name="name" Label="Name" DataType="DT_WSTR" Length="200" />  
      <Column Name="birthdate" Label="Birthdate" DataType="DT_DBDATE" />  
      <Column Name="extras.bornAt" Label="BornAt" DataType="DT_DBDATETIMESTAMP" />  
      <Column Name="extras.dollarsInPocket" Label="DollarsInPocket" DataType="DT_DECIMAL" />  
      <Column Name="extras.isAfraidOfSpiders" Label="IsAfraidOfSpiders" DataType="DT_BOOL" />  
      <!-- Newly added columns below -->  
      <Column Name="extras.hobbies" Label="FirstHobby" ValueTemplate="&lt;&lt;{$value$}|~|$.data[0].name,FUN_JSON_TO_TEXT&gt;&gt;" DataType="DT_WSTR" Length="200" />  
      <Column Name="extras.hobbies" Label="FirstHobbyYearsInterested" ValueTemplate="&lt;&lt;{$value$}|~|$.data[0].yearsInterested,FUN_JSON_TO_TEXT&gt;&gt;" DataType="DT_I4" />  
   </OutputColumns>  
</EndPoint>

Mapping is still done by using the Name attribute in the Column element, but also the ValueTemplate attribute is used to calculate the desired value:

• JSON array hobbies is mapped by setting Name to extras.hobbies

• First hobby's JSON property name is retrieved by applying FUN_JSON_TO_TEXT function to that array and setting this value in the ValueTemplate attribute:

  <<{$value$}|~|$.data[0].name,FUN_JSON_TO_TEXT>>

  It is simply an XML-escaped value of this string: <<{$value$}|~|$.data[0].name,FUN_JSON_TO_TEXT>>

• First hobby's JSON property yearsInterested is retrieved by applying FUN_JSON_TO_TEXT function to that array and setting this value in the ValueTemplate attribute:

  <<{$value$}|~|$.data[0].yearsInterested,FUN_JSON_TO_TEXT>>

  It is simply an XML-escaped value of this string: <<{$value$}|~|$.data[0].yearsInterested,FUN_JSON_TO_TEXT>>

Data returned after update