When downloading data from (cloud) platforms using OData, an error may be shown by Power BI (or Power Query) due to invalid JSON being retrieved:
DataSource.Error: OData: Invalid JSON. A comma character ‘,’ was expected in scope ‘Array’. Every two elements in an array and properties of an object must be separated by commas.
This error message indicates that the data retrieved using the JSON transport format is actually not in valid JSON format.
This topic provides you means to analyze this error by providing background and tools. Probably, not all possible causes are documented. Please add your problem as a new topic so when this topic does not provide you sufficient help. Include the URL of this topic in your new topic; Invantive and maybe other members of the community will try to assist you.
JSON and OData
The data downloaded is packaged in the so-called JSON format. JSON is a text representation and is reasonably human-readable. The OData format is an extension to JSON, which provides more context, but is still readable.
For use with Power BI, Power Query, Dataflows and Azure Data Factory Invantive Bridge Online heavily leans on OData version 4 since Microsoft provides an out-of-the-box provider. Additionally, the Microsoft OData driver offers good performance and scalability, including forwarding many filter types to the Invantive data sources to reduce network use.
Retrieve OData in JSON format
Most common, the actual error causing the problem is shown in the Invantive Bridge Online Monitoring, such as:
The most essential part is the error code, such as itgenoda198.
Next learn what JSON looks like and how to retrieve the JSON payload of the OData Bridge of Invantive Cloud using solely your browser.
Execute the following steps to download and show the JSON in your browser:
- Go to https://bridge-online.cloud.
- Log on using your user name and password.
- Click on “Databases”:
- Choose your database.
- An URL such as https://bridge-online.cloud/sample/odata4 will be opened.
- Each available table is named after
name:
- Choose a table, like
Dummy.Demo.PhoneCalls@dmy2. - Append the table name after the original URL, giving the URL with table data: https://bridge-online.cloud/sample/odata4/Dummy.Demo.PhoneCalls@dmy2.
- Open the new URL:
Error message
The error message above indicates that the JSON has an invalid structure. There are two common causes of the error “A comma character ‘,’ was expected”:
- An error occurred during downloading the data.
- Invantive Bridge Online returns invalid JSON.
Error during downloading the data
The JSON can be invalid since Invantive Bridge Online starts returning data to Power BI before all data has been processed. This approach is called “streaming” as opposed to “batching”, and considerably improves time to first row received and often also improves total download time.
An idea for Power BI has been submitted under https://ideas.powerbi.com/ideas/idea/?ideaid=448e7b93-897f-ed11-a76e-281878bd0909 (see list of suggested improvements at Power BI suggestions for improvement). If you would be helped by this change, please cast a vote on this improvement idea.
A disadvantage, especially with environments with hundreds of companies, is that some company’s data will not be processed early. When such a company has a setup error or even corrupt data, an error will be triggered.
This error is also inserted into the JSON to help you learn what is going wrong. The error is also registered in System Messages of Invantive Cloud.
The serialized version of the error might resemble something like:
{
"error":
{ "code": "itgensop057"
, "message": "Server was unable to process request. ---\u003E Geen bedrijf rooster Check with the custom error message instructions of the platform used. (itgensop057, 673216ae-1e63-4245-937b-ce436d2ba0e5)"
, "target": null
, "details":
[
{ "code": "673216ae-1e63-4245-937b-ce436d2ba0e5"
, "target": null
, "message": "Unique ID"
}
]
, "innererror": null
}
}
The error code itgensop057 is an essential piece of information to find and resolve the root cause.
Recommend approach to find the actual cause is to download the JSON manually in a browser as described or check the contents of the System Messages form. When the error message is unclear, it is recommended to add a topic to the Dutch or English questions category on the forums, including the error message code.
As an alternative, a query on the same table can be run using Invantive Query Tool or similar products such as the online SQL editor on Invantive Cloud.
Invantive Bridge Online returns invalid JSON
The known samples of the second category (invalid JSON returned by Invantive Bridge Online) are:
- Foutmelding OLE DB- of ODBC-fout: Invalid JSON. A comma character ',' was expected in scope 'Array' : a (historical and fixed) bug that caused Invantive Bridge Online to too eagerly terminate long-running downloads.
- Connectie Exact Online via Azure Data Factory geeft: The metadata document could not be read : a (historical and fixed) bug that caused large (100 MB+) downloads of Invantive Bridge Online to sometimes contain additional string text at the end.
Invantive Bridge Online driver
Invantive products allow connection to Invantive Bridge Online as yet another driver. The Invantive-products carefully try to extract the actual error message returned from Invantive Bridge Online starting release 22.0.470 and will show the respective error.