How to select all records and columns from salesforce using the query option in Azure data factory? (SELECT * FROM test ALL ROWS)

Question

In Azure data factory I want to retrieve all possible data there is in a salesforce table. Also, the archived and the deleted records. I try to do the following but it won't work:

SELECT * FROM test ALL ROWS

I also want the archived and the deleted rows of the test object in salesforce.

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 Id FROM test ALL ROWS<<< ??? >>>'.
SQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT Id FROM test ALL<<< ??? >>> ROWS'.,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 Id FROM Event ALL ROWS<<< ??? >>>'.
SQL error: [Microsoft][SQLEngine] (31480) syntax error near 'SELECT Id FROM test ALL<<< ??? >>> ROWS'.,Source=Microsoft Salesforce ODBC Driver,'

Answer 1

SELECT FIELDS(ALL) FROM Account will give you all columns you're allowed to see but will be capped at 2000 records. If you want to export more - you need to explicitly list the columns out, Salesforce doesn't support *.

ALL ROWS is just for apex, Salesforce's server-side language. Over API it should be just the queryAll call which you're already using. Try queryAll with SELECT Id, Name FROM Account WHERE IsDeleted = true and it should show you the records in the bin (if any)