Failed To Convert Value From BSON Type "ARRAY" To SQL Type "WVARCHAR"

[intapiuser]

Sharing a tip from our support team for resolving build failures when using custom REST Connector. 

The "Failed to convert value from BSON Type "ARRAY" to SQL Type "WVARCHAR"" error might happen with CustomREST connectors when the imported table contains complex object such as Json or object.

For example, let's assume we have a connector to our own Jira system.

Each item contains a labels field. As it might contain a few labels, instead of having all of them in one string, the connector creates a separate table for this field, which can be imported and connected to the main table by the _id field.

However, our REST connector is using a sampling mechanism, which checks the first rows of the imported data, and by these rows determines what is the type of the column.

Now, let's assume we have in our Jira system 10,000 items. However, the first items contain one label only.

In this case, the sampling mechanism will decide that the labels field type is string.

However, when the full build is running, it will eventually reach a record which contains more than one label. In this case, the build will fail on this error. Here is an example of it:

Solutions

1. Importing only the required records - In case these complex are not really relevant, just unimport them.
2. Increasing the MaxDocs value in the connectors settings object - usually, we set the MaxDocs value to 100. The MaxDocs determines how many rows are read by the sampling mechanism, so increasing it might resolve the issue.
3. Transforming the field to string type - This can be done by using the transformation object. For more info, go here: https://sisense.dev/guides/legacy/customRest/configuration/tables.html#transformation-object