question

ppg-8477 avatar image
0 Votes"
ppg-8477 asked SenapathyKumaraswamy-8069 answered

ADF not connecting to Salesforce with the last API version

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,'

azure-data-factory
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MartinJaffer-MSFT avatar image
1 Vote"
MartinJaffer-MSFT answered MartinJaffer-MSFT converted comment to answer

Hello @ppg-8477 and welcome to Microsoft Q&A.

Thank you for bringing this issue to our attention. I have now reported this to the product group. If I was to speculate, I'd say it sounds like this feature has not yet been integrated into the ADF driver.

While we wait for a reply, may I help unblock you by suggesting you try the SQL method?

The ADF Salesforce driver works with two query languages, the SOQL you used above, and SQL-92. The driver first tries to read the query as SOQL, and if that does not work, reads it as SQL.

The SQL query would be `SELECT TOP 1 * FROM <table_name>

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ppg-8477 avatar image
1 Vote"
ppg-8477 answered

Thank you for your answer. I also tried an SQL query, but I get an error that I believe is due to the big amount of fields, as when manually selecting one half of fields and then the other, both queries succeeded. In a similar test, the query failed when selecting more than ~600 fields, but no individual field was the cause. I have edited the full error into the question as I hit the comment character limit.
This happened both with "SELECT * FROM x" and "SELECT <list of fields> FROM x". In any case we may split the extraction into multiple tables to make them more manageable.

· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Oh, wow, I didn't expect the Select * to fail. I will definitely add that to my internal conversation. However, this suggests to me, even if the FIELDS(ALL) worked, you would still run into the other error.

600 fields is a LOT. I can see how things might choke, even sending 1 row at a time, especially if there are a lot of big strings or UIDs.

I have worked in big data before, but I never had a 600 field table. Is 600 typical of your data design, @ppg-8477 ?

1 Vote 1 ·

It's a lot of fields indeed. Not a typical amount by any means, it's by far the largest amount of fields of any table in our system (all the other tables were queried fine using SELECT *). Most of them are custom fields added over the years for specific uses and users, and we are definitely considering reducing that amount and splitting it into multiple tables in our azure system (we can't modify the Salesforce architecture as it's managed by a different team). In the Salesforce developer console I can query it fine using FIELDS(ALL), but I understand that this size could cause problems when piping the query through the API/ADF driver.

0 Votes 0 ·

@ppg-8477 I concur that splitting the table would be the best course of action. In addition to working around this issue, your data would load faster and in general be more manageable.

I did raise the the failure of select * with the product group. The road to resolving that would involve much back-and-fourth, and take longer to resolve than splitting the table. Let me know whether you want to pursue this, or just split the table.

The FIELDS(ALL) is under discussion now.

0 Votes 0 ·

In the end we will be splitting the table in the extraction, as it was going to be split anyway in a higher layer of processing. It would still be interesting to have the FIELDS(ALL) functionality to use in our other tables though. In any case, thanks for your help!

1 Vote 1 ·
SenapathyKumaraswamy-8069 avatar image
0 Votes"
SenapathyKumaraswamy-8069 answered

Hi @MartinJaffer-MSFT , I too facing the same error while using the "SELECT FIELDS(ALL) FROM table limit 1" in data factory with the salesforce API version of 54. after reading the above conversation, eager to know whether product group fixed this failure(having a patch) or having this piece in plate for future? I'd appreciate any update on this.

Kind Note: I am referring to below statement on your comment:
"I did raise the the failure of select * with the product group. The road to resolving that would involve much back-and-fourth, and take longer to resolve than splitting the table"

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.