Adding SSIS Components to Business Intelligence Development Studio's Toolbox

SSIS Integration Toolkit for HubSpot includes two data flow components for use with HubSpot. They need to be added to the SSIS toolbox before you can use them in a SSIS data flow task.

Note: If you are using SQL Server 2012 or later development environment, you should not need to do this, as SQL Server 2012 or later automatically lists all available pipeline components by scanning the system.

To add the data flow components, create a new data flow task if you do not have one yet and switch to the SSIS data flow page. Right-click on the toolbox area to bring up the context menu, where you can select the "Choose Items..." option as shown below.

Choose items

You will be presented with a window called "Choose Toolbox Items". Switch to "SSIS Data Flow Items" tab, and select HubSpot Destination and HubSpot Source components from the list.

Add HubSpot SSIS Components

Both data flow components should now appear in your SSIS Toolbox, where you can drag and drop any of them to the design surface of your SSIS data flow task.

Using the HubSpot Source Component

The HubSpot Source Component is an SSIS data flow pipeline component that can be used to read / retrieve data from HubSpot.

The component includes the following two pages to configure how you want to read data from HubSpot.

  • General
  • Columns

General page

The General page of the HubSpot Source Component allows you to specify the general settings of the component.

HubSpot Source Editor

Connection Manager

The HubSpot Source Component requires a HubSpot connection in order to connect with HubSpot. The Connection Manager drop-down will show a list of all HubSpot connection managers that are available to current SSIS package.

Source Object

The Source Object drop-down lists all of the currently supported objects which you can read from.

Batch Size

The Batch Size lets you specify how many records to retrieve per service call to HubSpot. Some Objects, however, retrieve all records in 1 service call. The Batch Size field will become disabled for objects such as this.

Note: When the Batch Size property is disabled in the HubSpot Source Component, it means that for the particular Source Object you have selected it will make a single request to retrieve all of the records.

Output Timezone

The Output Timezone option lets you specify how all datetime fields should be retrieved. The default value is UTC. There is also the option to convert to Local Time. There is no performance difference between the two options, it simply makes integration easier.

Recent Created Only

The Recent Created Only option can be used to get all records that have been created in the last 30 days, or the 10k most recently created records.

Note: This option will only be available to Company, Contact, Deal, and Engagement object.

Note: Contact object is not limited to the 30 days range, in which case you may need to specify the sinceDateTime Filter Parameter to get the recently created contacts.

Recent Modified Only

The Recent Modified Only option can be used to get all records that have been modified in the last 30 days, or the 10k most recently modified records

Note: This option will only be available to Company, Contact, Deal object.

Filter Parameters

You can specify filtering parameters so that the source component only returns those records that satisfy such filtering parameters. Filter fields vary from object to object along with the criteria. The different filter criteria include:

  • Equals: all records with a particular field that exactly matches the supplied value
  • Greater than: all records with a particular field greater than the supplied value (generally a timestamp)
  • Less than: all records with a particular field less than the supplied value (generally a timestamp)
  • Contains: all records with a particular field that contains the supplied value (Keyword object only)
  • Smart Search: all records whose name (first or last) or email begins with the supplied value. (Contact object only)
    • Example: If the supplied value was "hub", the HubSpot Smart Search would return any contact whose first or last name began with "hub" (such as "HubSpot"). It would also return any contact whose email contains "hub" in either the beginning of the username or the domain portion of the email (such as "[email protected]" or "[email protected]").
Refresh Component Button

Clicking the Refresh Component button causes the component to retrieve the latest metadata and update each field to its most recent metadata.

Expression fx Icon

Click the blue fx icon to launch SSIS Expression Editor to enable dynamic update of the property at run time.

Generate Documentation Icon

Click the Generate Documentation icon to generate a Word document which describes the component's metadata including relevant mapping, and so on.

Columns

The Columns page of the HubSpot Source Component shows you all available attributes from the object that you specified on the General page.

HubSpot Source Editor

The HubSpot Source Component contains a special feature to toggle between the decimal and bigint data type. By default, all numeric fields are assigned a data type of decimal (excluding system fields that have a known type of bigint). If you wish to set specific fields to a data type of bigint (fields in which you are 100% sure are integers), simply click the toggle button. This saves the user from having to go into the advanced editor to do the same thing. This feature exists due to HubSpot API limitations.