Azure Data Factory Salesforce connector

Question

I am using the ADF Salesforce connector to query a Salesforce table. I am able to execute a query that uses the SOQL syntax, but SOQL does not offer SELECT * so I am attempting to use the SQL format as described by the ADF Docs here:
Copy activity properties
For a full list of sections and properties that are available for defining activities, see the Creating pipelines article. Properties like name, description, input and output tables, and various policies are available for all types of activities.

The properties that are available in the typeProperties section of the activity, on the other hand, vary with each activity type. For Copy Activity, they vary depending on the types of sources and sinks.

In copy activity, when the source is of the type RelationalSource (which includes Salesforce), the following properties are available in typeProperties section:

COPY ACTIVITY PROPERTIES
Property Description Allowed values Required
query Use the custom query to read data.

A SQL-92 query or Salesforce Object Query Language (SOQL) query. For example: select * from MyTable__c. No (if the tableName of the dataset is specified)

Source: https://learn.microsoft.com/en-us/azure/data-factory/v1/data-factory-salesforce-connector#copy-activity-properties

When I write the query as "SELECT *" I get an error stating:

ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure: SOQL error: [Microsoft][SQLEngine] (31480) syntax error near 'Select *<<< ??? >>> From Account

This error clearly indicates to me that ADF is using a SOQL query.

I would like to use a SQL query but I cannot find any documentation that explains how to do so.

Anyone know how to do this? Or is the documenation inaccurate?

Answer 1

Any updates on the SOQL error: [Microsoft][SQLEngine] (31480) syntax error ?

Answer 2

I was having this same issue and in my case it looks like the API is trying to parse the Case table as the keyword for a case statement. Putting the table name in brackets resolved it for me

SELECT * FROM [CASE]

Answer 3

I had a similar issue. We tried Select * it would not work. Then FIELDS(ALL) and it did not work. Finally, I realized the issue : ADF Supports both SQL and SOQL but you cannot specify whether you are attempting to use SQL or SOQL. Instead ADF determines which language to use by your syntax. Well, if you inadvertently use SQL syntax when trying to write SOQL or Vice Versa you will have a syntax error. In my case I was providing the date in a format that made ADF think I was writing SOQL therefore causing my Select * not to work. However, when I used a date format supported by the ADF SQL, Select * worked. I hope this helps prevent some of the headaches that I had on this.

Answer 4

The following works:

SELECT * FROM "case"

You need to add double quotes because the word Case is reserved in SQL. This will work when querying the Salesforce Case object.