How to utilize ADF to enrich source data to Salesforce

Question

Hello, I am using Azure Data Factory to enrich some CSV data that I am using a pipeline to have stored in an Azure Database.

I want to query my Salesforce repository and enrich the CSV data with some fields from the contact API object. I'll then take that enriched data from both repositories and store it in a table.

I thought a good way to do with would be to do a look up for the IDs (Primary Key) of my Azure database and store the result set as a variable array.

Then, when I am doing a copy data action in my pipeline, I would reference the array variable within the SOQL statement for Salesforce. Example :

SELECT ID, division_code__c, team__c
FROM Contact
WHERE ID IN (@variables('PrimaryKeyColumn'))

I am getting this error when trying to use the variable : "The function call must take the completion string." I guess this makes sense since the array may not be parameterized with commas to fit into the where clause for 'IN."

Could someone help me understand if I am doing this wrong for comparing data to Salesforce? I do want to do this most efficent way, but noticed in the data flows factory resource I can't reference Salesforce as a dataset.

Answer 1

This kind of error occurs when there's a mismatch in the expected format of the query parameters.

In your WHERE clause you are using an array so you may need to use a Set Variable activity to construct a string from your array that fits the SOQL query format and then update your query :

SELECT ID, division_code__c, team__c
FROM Contact
WHERE ID IN (@{variables('FormattedPrimaryKeyString')})

Answer 2

@Mike Welcome to Microsoft Q&A forum and thanks for reaching out here.

As per my understanding looks like the issue seems to be with your Query formation using the dynamic expression to pass in the array values.

I do not have a Salesforce instance to replicate the same but, I have tried with SQL and was able to overcome the issue by forming a query as below:

I have a array variable as below and formed a SQL query as below using dynamic expression to make it work

select * from dbo.employeelookup where employeeid in  @{replace(replace(string(variables('varIDs')),'[','('),']',')')}

The above expression will form a query as below during runtime:

{
    "source": {
        "type": "AzureSqlSource",
        "sqlReaderQuery": "select * from dbo.employeelookup where employeeid in  (23,24,25)",
        "queryTimeout": "02:00:00",
        "partitionOption": "None"
    },
    "dataset": {
        "referenceName": "AzureSqlTable1",
        "type": "DatasetReference",
        "parameters": {}
    },
    "firstRowOnly": false
}

If you still continue to see this issue, kindly share your Lookup output JSON payload so that I can share the correct expression.

Hope this helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.