The CData SSIS Components for Zoho CRM models Zoho CRM data as an easy-to-use SQL database with tables, views, and stored procedures.

The component exposes three schemas:

• See v2 for the available entities in version v2 of the ZohoCRM API.
• See v2.1 for the available entities in version v2.1 of the ZohoCRM API.
• See v4 for the available entities in version v4 of the ZohoCRM API.

Batch Operations

The maximum batch size supported by ZohoCRM's API is 100. Therefore, CData recommends setting the BatchSize connection property to this value. CData does not recommend using a larger value, because it does not improve the performance of the component.

Zoho CRM APIs

The Zoho CRM component supports the following APIs to query data from the source.

• Query API: Uses COQL to query data from Zoho CRM. To use this API, set UseCOQL to True.
• Bulk API: Uses the Bulk API to query data from Zoho CRM. To use this API, set UseCOQL to False and APIType to Bulk.
• REST API: Uses REST endpoints to query data from Zoho CRM. To use this API, set UseCOQL to False and APIType to REST.

API Limitations

Zoho CRM imposes limits on the maximum number of rows and columns that interfacing tools such as the CData SSIS Components for Zoho CRM can retrieve from its APIs.

The limits vary based on the API you are using. These limits are:

• Query API: Maximum of 100,000 rows and 50 columns.
• Bulk API: Over 100,000 rows and no more than 200 columns.
• REST API: Maximum of 10,000 rows. v2 and v2.1 have no column limit, but v4 is limited to a maximum of 50 columns.

API Metadata

The columns and values retrieved from tables and views vary based on the API you are using. These variations are:

• Query API
  • For Lookup type columns, the *_Id is displayed and has a value, but *_Name doesn't appear.
  • For *Owner and *ModifiedBy columns, *_Id, *_firstname, and *_lastname are displayed and have values, but *_Name doesn't appear.
• Bulk API
  • For Lookup type columns, the *_Id and _Name are displayed, but only *_Id has a value.
  • For *Owner and *ModifiedBy columns, *_Id, *_Name, *_firstname, and *_lastname are displayed, but *_Name has no value.
• REST API
  • For Lookup type columns, the *_Id and _Name are displayed and both have values.
  • For *Owner and *ModifiedBy columns, *_Id, *_Name, *_firstname, and *_lastname are displayed, but *_firstname and *_lastname have no values.

Using Query Processing

The component offloads as much of the SELECT statement processing as possible to the Zoho CRM APIs and then processes the rest of the query in the component. The following sections document API limitations and requirements.

Tables

The component connects to Zoho CRM and gets the list of tables and the metadata for the tables by calling the appropriate Web services. Any changes you make to your Zoho CRM account, such as adding a custom module, adding a custom field, or changing the data type of a field, occur immediately when you connect using the component. The Tables sections show an example, based on the Zoho CRM development environment, of what the entities in your account might look like.

Note: The Notes table does not support the Bulk API. If you are using the Bulk API, the component uses the REST API instead.

Views

Most views are defined in static configuration files. Custom views are not. Instead, custom views are dynamically retrieved by connecting to Zoho CRM, calling the appropriate Web service and retrieving the list of custom views from the metadata returned. Any changes you make to your Zoho CRM account, such as adding a new custom view, occur immediately when you connect using the component.

To use custom views, set the IncludeCustomViews property to true. Its default value is false since custom views introduce an overhead when retrieving the list of tables and views.

Criteria applied in the custom view (when creating or when editing it in the UI) are preserved in CData Zoho CRM component. Nonetheless, further filtering on records is available at any time.

To use relative views, set the IncludeRelatedLists property to true. Its default value is false since related views introduce an overhead when retrieving the list of tables and views.

The EntityId field is required to fetch related module data. This field refers to the parent module Id.