Convert Sql queries to SOQL using ADF

Question

I have an existing adf pipeline which uses lookup activity and get all the table names from azure sql sever. Using for each activity I copy the data from sales force to blog storage. I use dynamic sql queries in copy activity . Select * from item().table_name . This works well. Since sales force legacy is depreciated and new salesforce connector doesn’t support SQL . It is mandatory to use SOQL . The column names changes so I want to use select * but this is not supported . I want to find and get the latest column for each entity dynamically and replace select * from item().table_name

Answer 1

Hi Prasanna, Prashanth (RIS-CON) ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand that you are looking to convert SQL queries to SOQL which would be an equivalent query for 'select * from table' in SQL , to dynamically query all the tables of salesforce which is stored in the lookup table.

You can store the query for each of the tables in the metadata table itself in a 'query' column like below, to iterate through the query column within foreach:

SELECT Id, Name, Field1__c, Field2__c, ... FROM TableName__c

To get the columnnames present in each object, you can query the Schema Builder . However, there isn't a direct equivalent to the "information_schema.tables" or sys tables in SQL, which provides metadata about tables in a database.

To fetch the list of all the columns in Salesforce, you can use the Tooling API or describe calls in Apex. Here's how you can do it using Apex:

// Get the describe information for the object
Map<String, Schema.SObjectType> gd = Schema.getGlobalDescribe();
Schema.SObjectType sobjType = gd.get('YourObjectName'); // Replace 'YourObjectName' with the API name of the object you want to describe
Schema.DescribeSObjectResult r = sobjType.getDescribe();

// Get all fields of the object
List<Schema.SObjectField> fields = r.fields.getMap();
for (Schema.SObjectField field : fields) {
    // Print or process the field information
    System.debug(field.getDescribe().getName()); // This will give you the API name of the field
}

This Apex code will retrieve all the fields (columns) of the specified object and output their API names. You can run this code in the Developer Console or any other appropriate environment where you can execute Apex code.

Keep in mind that you need the appropriate permissions to execute Apex code and access the Schema information. Additionally, ensure that you replace 'YourObjectName' with the actual API name of the Salesforce object you want to describe.

Relevant resource: https://salesforce.stackexchange.com/questions/56868/salesforce-com-how-to-find-all-tables-and-columns-like-sql-sys-tables-sys-co

Hope it helps. Thankyou

Answer 2

If you are comfortable with Salesforce REST API, you can use it to retrieve your metadata.

You can use the Describe endpoint (/services/data/vXX.0/sobjects/ObjectName/describe/) to get metadata for each object : https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/resources_sobject_describe.htm

With parsing the response, you will be able to extract field names from the API response.

After retrieveing the metadata, you can construct a dynamic SOQL query for each object : https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/apex_dynamic_soql.htm

You can use :

• an expression to concatenate all field names into a SOQL query string.

SELECT Field1, Field2, Field3 FROM ObjectName

• a ForEach activity in ADF to loop over each object and use the constructed SOQL query to pull data.