Type conversion error using Azure Data Factory HubSpot Connector
EXEC SUMMARY
I'm pulling in data from HubSpot using the Azure Data Factor HubSpot connector. Generally works fine but for two of the available entities, I get the error below when previewing or pulling data; I've isolated the fields provoking the error and my workaround is to skip them but I'd rather not.
Two questions:
(a) any insights on the problem?
(b) working with HubSpot support I was directed to this forum but also a firm called ZappySys. Their site doesn't seem to mention the ADF HubSpot connector as one of their products, only SSIS connectivity. Anybody have insights into that?
(1) when I try to preview data within Azure Data Factory, I get this (only for the two tables, others work fine):
ERROR [HY000] [Microsoft][Hubspot] (120) Error converting 0.0 to type boolean. Activity ID: 6a89d90d-0d06-4c34-80f0-bc76614b23da
(2) when I create a pipeline with a Copy activity and set it to auto-create a table on the SQL-Server side, I get this error (but only for the two tables, others work fine):
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Hubspot] (120) Error converting 0.0 to type boolean.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Hubspot] (120) Error converting 0.0 to type boolean.,Source=HubspotODBC_sb64.dll,'
(3) I did some exploration and noticed that these are the columns auto-created on the SQL-Server target side for the two tables:
The three marked with a star are bit datatype in SQL.
When I set up my Copy process in the pipeline with an explicit SELECT statement, and include all columns -- same error. But when I remove the last three columns, the error goes away. So evidently those are the ones affected by the problem on the source side.
Type conversion error using Azure Data Factory HubSpot Connector
Hello @MMurad5, thank you for posting in our Community!
I wanted to check if you reached out to Microsoft support. Looks like this error could be coming from their end, and they will be the best resource to reach out to (More information here)
Some previous cases of it users mentioned that the error was fixed because "they were not selected enough scopes in the authentication of my app."
Type conversion error using Azure Data Factory HubSpot Connector
Thanks @PamCotton I have indeed open a ticket with Microsoft, for this, and some other errors we received when pulling data, including one saying "URI too long". I've also been working with HubSpot support