SSIS Integration Toolkit for HubSpot

Premium Service Lookup for Hubspot

SSIS Integration Toolkit for Hubspot offers a Premium Service Lookup component that provides lookup capabilities by searching records in HubSpot instances. The Premium Service Lookup (also short for "PSL" below) component was first introduced in our v21.1 release; any prior versions will not have the component.

The Premium Service Lookup component is a shared component that is shipped with a few other integration toolkits including SSIS Productivity Pack, SSIS Integration Toolkit for Microsoft Dynamics 365, and SSIS Integration Toolkit for HubSpot, since the v21.1 release.

In order to use the Premium Service Lookup component to look for data from a HubSpot instance, you will need to have the toolkit installed. Once installed, you may continue with the configuration of the component using the following four different pages.

• General
• Lookup Conditions
• Output Columns
• Error Handling

General Page

The General page can be used to configure the general configuration settings for the Premium Service Lookup component.

Connection Manager

A Connection Manager is required for the Premium Service Lookup component to connect to. A list of available connection managers will populate the drop-down.

Target Object

The Target Object can be selected from the drop-down list. This would be based on the Connection manager. The target object would be used as the lookup table for the input data.

Max Returned Matches(since v24.1)

The Max Returned Matches option allows you to specify the number of matched records to be returned. The default setting of the option is 1, which should be most commonly used option for typical use cases. When the option is set to be greater than 1, the component will change to be an asynchronous component. When the option is changed to 0, it will return all matching records. If it happens to be changed back to 1 again, the component will work as a synchronous component.

Cache Mode

You can select a cache mode from the below options.

• Full Cache: When chosen, the component will populate a full cache of all records from the target object. This is the preferred option when the number of records in the target object is small.
• Partial Cache: When chosen, the component will gradually build up lookup cache as the data load progresses. This is the preferred option when the number of records in the target object is significantly large.
• No cache: No cache is built when this is chosen.

Note: The cache mode would be available based on the Target object chosen. Some objects and queries do not support one or more cache modes, which would be reflected in the component.

Note: As much as possible, the Partial Cache or No Cache option should be avoided for performance reasons, since the lookup will be performed on a row-by-row basis.

Advanced Text Matching Options

The Advanced Text Matching options are as follows

• Ignore case: When chosen, the Lookup will perform a case-insensitive lookup. For instance, "ABC Company" will be treated the same as "abc company".
• Ignore Leading Whitespace: When chosen, the leading whitespaces will be ignored.
• Ignore Symbols: When chosen, any symbols would be ignored.
• Ignore Trailing whitespaces: When enabled, any trailing whitespaces would be ignored.
• Ignore Diacritics/Accents: When chosen, the Lookup will not take any diacritics within a string into consideration.

Note: The Advanced Text Matching Options only apply to Full Cache mode.

Use HubSpot Search API

Enable this option to use HubSpot Search API for performing the lookup.

Note: When using the Search API option, the Full Cache option will have to be disabled since the Search API option is designed to perform the search row by row. We don't generally recommend using the Partial Cache or No Cache option for performance reasons. In addition, HubSpot might have a more restricted API rate when Search API is used. In choosing this option, you might have to create a separate connection manager which uses a lower throttling rate.

Batch Size

The Batch Size option allows you to specify how many records you want to retrieve each time. The default value is 100. The maximum allowed value is 1000.

Lookup Conditions Page

The Lookup Conditions page can be used to configure the lookup settings for the Premium Service Lookup component.

Condition Builder Mode

The Condition Builder Mode provides a grid for creating a query that can be used as the lookup.

The Query Editor supports adding filter conditions.

• Add condition (+): Adds a condition to be used in a query.
• Remove condition (-): Removes the condition that was added.
• Arrows: Use arrows to group the lookup conditions.
• AND/OR: Specify AND or OR to create logical expressions of your lookup conditions.
• Lookup Column: Select the lookup column from the drop-down list which displays available columns for the specified Lookup Table.
• Operator: Use the query operator to specify how each input value in a clause must relate to the corresponding value in a lookup table.
• Input Value: The Input Value for the lookup condition. Available options are:

• Input Column: Enables user to perform Lookup match by selecting a field from the Lookup Table Column

  Variable: Enables user to perform Lookup match based on a System or User SSIS variable

  Static Value: Enables user to perform Lookup match based on a static value

Custom Query Mode

Switching the query designer to Custom Query Mode will allow you to freely edit the query text. This can help in some cases where certain advanced queries may not be supported in the condition builder.

Output Columns Page

The Output Columns page allows you to add columns from the lookup entity to be returned as output columns for the lookup component.

There are two Additional Output Columns that can be used for validating the outputs.

• _MatchFound – This field returns true if any matching records have been found in the lookup object or system. Or otherwise, it should return a false.
  
• _HasMoreThanMaxReturnedMatches – This field will be true if there are more matching records in the lookup object or system than the specified Max Returned Matches option, or otherwise it should return a false value.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen.

There are three options available that you can choose for the component's error handling:

1. Fail on error
2. Redirect rows to error output
3. Ignore error