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.

Answer 3

Kranthi,

Thank you for your reply. I think I see what you are doing, but here's a more detailed overview of what I am trying to accomplish.

1. Data is copied out of CSVs and stored in an Azure Database
2. A lookup is done on a column (let's just call it ID's here)
3. A foreach activity is implemented, and it's taking in the following expression below from the lookup

@activity('Lookup').output.value

4. Inside of the foreach loop, our first activity is to dynamically append a variable - named 'ConcatenatedIDs' - and prepare it for our SOQL query. I'm using this syntax below to prepare a variable for each row by row extraction

@concat(item().provider_entity_id, ',')

5. Finally, inside of our SOQL statement within the same foreach loop, I would use a copy activity to withdraw the row by row records in a Salesforce compliant query.

SELECT ID, Division_Code, Team
FROM Contact WHERE ID IN ('@{variables('ConcatenatedIDs')}')

All of the steps work beautifully, storing each string variable from my table into a variable through each loop correctly. The issue arises when I pass the statement from step 5 to Salesforce as a string.