Azure Data Factory Salesforce connector

Michael Schulz 1 Reputation point
2020-08-12T20:01:12.207+00:00

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?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,534 questions

9 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anudeep 6 Reputation points
    2021-02-09T14:07:01.123+00:00

    in salesorce spring21 release we may find an option to select all columns from an object using FIELDS(ALL),FIELDs(STANDARD),FIELDS(CUSTOMIZED).. So you can try the option of "select FIELDS(ALL) from Account" probably after 12th of this month...

    1 person found this answer helpful.
  2. Benjamin david 1 Reputation point
    2020-10-01T10:10:19.553+00:00

    Any updates on this please, me too experience the same issue. Trying to extract all fields in the copy activity from Salesforce connector in ADF V2.

    0 comments
  3. Keshavi Patel 1 Reputation point
    2020-11-27T15:29:30.687+00:00

    @KranthiPakala-MSFT , I'd like to report the same error. I am observing the same behavior as originally reported and would like to leverage the promised ability of ADF to use SQL query in the pipeline.

    Even a simple select * from case is generating the error - ERROR [HY000] [Microsoft][Salesforce] (120) SOQL_FIRST mode prepare failure. I tried for the copy utility. Surprisingly it does not give the error for case history. Attaching screenshots for reference.
    43383-case-error.jpg

    43392-casehistory-good.jpg

  4. Govind Patwa 1 Reputation point
    2022-02-11T05:32:52.087+00:00

    Salesforce does support SELECT * in SOQL. In Azure Data Factory connector does Select * does not work, no matter what you try. I am also having the same problem

    0 comments
  5. Jean D. Lustria 1 Reputation point
    2022-04-08T10:22:28.08+00:00

    Any updates on this please? As we are also encountering this issue on the Case object - throwing this error:191239-case-error.jpg