I'm trying to retrieve some data from Salesforce using the integrated connector in Azure Data Factory. As one of the tables I'm querying has a large amount of fields (600+), I intend to use the FIELDS(ALL) functionality introduced in the Spring '21 Salesforce API (v51.0). This is because manually inputting all the field names in the query results in an error, probably because of the query size limit. The problem is, I'm getting a syntax error response when executing the query from ADF.
The full error message, in case that helps:
Failure happened on 'Source' side.
ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR
[HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:
SOQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT
FIELDS(<<< ??? >>>ALL) FROM <table_name> LIMIT 1'. SQL error:
[Microsoft][SQLEngine] (31480) syntax error near 'SELECT
FIELDS(ALL)<<< ??? >>> FROM <table_name> LIMIT
1'.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR
[HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure:
SOQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT
FIELDS(<<< ??? >>>ALL) FROM <table_name> LIMIT 1'. SQL error:
[Microsoft][SQLEngine] (31480) syntax error near 'SELECT
FIELDS(ALL)<<< ??? >>> FROM <table_name> LIMIT 1'.,Source=Microsoft
Salesforce ODBC Driver,'
The full query I'm trying is SELECT FIELDS(ALL) FROM <table_name> LIMIT 1 (as the table is very large I'm limiting it for this test, I just want to see all the fields).
In ADF, I have manually set the API version to 51.0, and I've checked in Salesforce the new API versions are available (through <org_name>.my.salesforce.com/services/data and the generated WSDL file).
I'd appreciate any help or explanation for this issue.
Edited, extra error for comment:
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][Support] (50090) Conversion from string to number failed with value '',Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [HY000] [Microsoft][Support] (50090) Conversion from string to number failed with value '',Source=Microsoft Salesforce ODBC Driver,'