Unable to use a variable with a SOQL query in ADF

Question

We are trying to switch our ADF pipeline from using the old SFDC dataset which used Query to the new dataset which uses SOQL. In the process, we found out that it does not support field aliasing which had made our life easier. But now, to go around that aliasing problem, we wanted to use a variable in the SOQL query. But it does not seem to like it. Are variables in a SOQL not supported? Below is our SOQL code

We're trying to replace the portion of the code within the red box to be a variable. When I assign this code as a string to a variable, it shows the right verbiage but within SOQL itself, it does not like it.

Any assistance is appreciated.

Thank you in advance.

Answer 1

In ADF, when you work with SOQL queries, it's important to know that SOQL does not natively support dynamic variables the way some other query languages do. ADF expressions (like @{}) are not part of the SOQL syntax and therefore cannot be directly evaluated inside the SOQL query string. This is why your query isn't working as expected.

A workaround is to construct the entire SOQL query string dynamically in ADF, and then pass it as a single query string rather than embedding expressions directly in SOQL.

1. Create the query string dynamically in ADF using pipeline expressions before executing the SOQL. This can be done by building the query in an ADF pipeline variable using concat() to dynamically insert values (e.g., dates, numbers).
2. Assign the dynamically constructed SOQL query to a parameter or variable and use it in your Salesforce connector's dataset or activity.

{

    "name": "QueryString",

    "value": "@{concat('select * from ', json(item().SourceObjectSettings).objectApiName, ' where ', json(item().DataLoadingBehaviorSettings).watermarkColumnName, ' > ', if(contains(json(item().DataLoadingBehaviorSettings).watermarkColumnType, 'Int'), json(item().DataLoadingBehaviorSettings).watermarkColumnStartValue, concat('{ts''', formatDateTime(json(item().DataLoadingBehaviorSettings).watermarkColumnStartValue, 'yyyy-MM-dd HH:mm:ss'), '''}')), ' and ', json(item().DataLoadingBehaviorSettings).watermarkColumnName, ' <= ', if(contains(json(item().DataLoadingBehaviorSettings).watermarkColumnType, 'Int'), activity('GetMaxWatermarkValue').output.firstRow.CurrentMaxWaterMarkColumnValue, concat('{ts''', formatDateTime(activity('GetMaxWatermarkValue').output.firstRow.CurrentMaxWaterMarkColumnValue, 'yyyy-MM-dd HH:mm:ss'), '''}')))}"

}

In this example:

• The entire SOQL query is built as a string using ADF expression language.
• The result is stored in a variable (or passed as a parameter) and then used in your activity to execute the SOQL query.

This way, you're dynamically constructing the SOQL query in ADF and sending it as a whole, avoiding the issue of embedding ADF expressions directly inside SOQL.

Let me know if you need further adjustments!