Help Manual - Zoho CRM Source

Documentation on SSIS Zoho CRM Source component

Using the Zoho CRM REST Source Component

The REST Source component is an SSIS source component that can be used to read/retrieve data from Zoho CRM REST services and produce column data, which then can be consumed by a downstream SSIS pipeline component.

See Zoho CRM Rest Connection Manager for how to connect to Zoho CRM and Zoho CRM REST Destination Component for how to write to Zoho CRM.

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

  • General
  • Columns

General Page

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

SSIS Zoho CRM Source Component

Connection Manager

The source component requires a REST connection to Zoho CRM. The Connection Manager drop-down will show a list of all connection managers that are available for your current SSIS package.

Source Type

The Source Type can be chosen from the drop-down. There are two options:

  • Object
  • Query
Source Object

The Source Object drop-down lists available objects which you can read from.

Endpoint

The Endpoint drop-down lists all available rest endpoints for the selected object.

Endpoint Details

The Endpoint Details show the format of the request from the specific endpoint.

Batch Size

The Batch Size lets you specify how many records to retrieve per service call to Zoho CRM.

Note: Some objects do not support batch size, so this option will become disabled.

Output Time Zone

The Output Timezone option lets you specify how all datetime fields should be retrieved. Available options are:

  • UTC
  • Local
Custom View (Only for Bulk Related Endpoints)

The Custom View shows the available ones for the specific bulk endpoint and object.

Filter Parameters

You can specify Filtering Parameters so that the source component only returns those records that satisfy such filtering parameters.

Note: Filter fields may vary from object to object depending on endpoints.

Search Query (Available for Source Type as Query)

The Zoho CRM API provides the Query API option in which you can enter a COQL Query and fetch the records. Please refer to the API documentation on CRM Object Query Language(COQL).

SSIS Zoho CRM Source Component - Query

Please find an example below.

SELECT First_Name, Last_Name, Full_Name
FROM Leads
WHERE Last_Name = 'Anderson' and First_Name = 'Danyka'

Here are a few things to keep in mind:

  • COQL supports only SELECT query with the clauses WHERE, FROM, ORDER BY, LIMIT, and OFFSET.
  • COQL keywords are not case-sensitive. SELECT is the same as select.
  • By default, the system sorts the records in ascending order based on the record ID, if you do not include order by in the query.
  • The default value for LIMIT is 200 and OFFSET is 0.
Bulk Job Interval (secs) (Available for Bulk related endpoints)

The Bulk Job Interval determines the frequency rate to poll the status of the job to determine if it has been completed.

Criteria

The criteria can be provided when the Bulk feature is used. This will be in JSON format, and the API allows up to a maximum of 25 criteria.

SSIS Zoho CRM Source Component - Bulk Read

Criteria Designer

By clicking on the “+” button in the criteria grid, you can open the criteria designer. This Criteria Designer can be used to design Zoho CRM criteria intuitively. However, the designer does not currently support creating nested criteria. If needed, nested criteria can be achieved by making edits to the criteria in the main window.

SSIS Zoho CRM Source Component - Criteria Designer

  • Group_Operator: You can choose between blank, AND and OR operator
  • Field: The fields available from the Source object can be specified here.
  • Comparator: The below comparators are available
    • Equal
    • Between
    • Contains
    • Ends_with
    • Greater_equal
    • Greater_than
    • In
    • Less_equal
    • Less_than
    • Not_between
    • Not_contains
    • Not_equal
    • Not_in
    • Starts_with
  • Value: The value that needs to be evaluated against the field
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.

(Since v24.1) By clicking on Refresh Component, a new window pops up as shown below, that will have three options to decide how the updates to metadata should be performed.

REST Source - Refresh Metadata Settings.png

  • Update all: This would update all the fields regardless of whether there are metadata changes.
  • Update if there are incompatible metadata: This option would refresh any incompatible metadata to the discovered metadata.
  • Update none: No metadata updates would happen to existing fields.
Expression fx Icon

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

Generate Documentation Icon

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

Columns Page

The Columns page of the REST Source Component shows you all available attributes from the endpoint that you specified on the General page. On this page, you can select the output whose columns you wish to configure in the top left drop-down.

SSIS Zoho CRM Source Component - Columns

On the top left of the grid, you can see a checkbox, which can be used to toggle the selection of all available fields. This is a productive way to check or uncheck all available fields.

More fields

This button will open the Manage Additional Fields editor where you will be able to select additional outputs or fields from the returned object from the server.

SSIS Zoho CRM Source Component - More Fields

  • Expand All: This button will expand the tree view of all the fields under each object.
  • Collapse All: This button will collapse the tree view to only show the sub-objects of the returned JSON document.
  • View: This option enables you to view or hide the Details and Summary sections of the Manage Additional Fields editor.
  • Filter: This option allows you to only show checked or unchecked items and it can be used to filter the list of fields using a keyword.
  • Details section: This section displays the information of the selected field/output including the expected performance impact of the selected item if enabled.
  • Summary section: This section displays a summary of what will be added to the component in terms of secondary outputs or fields based on the selected items.
The Columns Page grid consists of:
  • REST Field: Column that will be retrieved from Zoho CRM REST service.
  • Additional Field Details: An information icon will appear here if the column is special. Hover over the icon to see what makes the column special (IF ANY).
  • Data Type: The data type of this field.

Note: As a general best practice, you should only select the fields that are needed for the downstream pipeline components.