How to pull salesforce data using soql in ADF by passing a variable?

Question

0

I'm trying to use the output from a dataflow (a comma separated string of IDs), then set as a variable, and pass the variable into a SOQL query IN clause. However, the SOQL clause won't accept the string. Somehow it turns to an array. variable outputPipelineDataflow outputDataflow and sink previewSOQL Query

I have tried to write the output from dataflow to the variable but the SOQL query won't accept the variable. Unsure what part went wrong. In the data flow I removed '[',']' and replaced " with ' .

Answer 1

@Bolun Eric Cui

Thanks for reaching out to Microsoft Q&A.

The issue you're facing is related to how SOQL handles strings used in the IN clause. Here's why the SOQL query doesn't accept your variable and how to fix it:

1. Problem:

• SOQL expects individual values separated by commas within the IN clause, not a single string containing multiple comma-separated values.

2. Solution:

There are two ways to address this:

A. Split the string into individual values and use a loop:

1. Split the string into an array: Use a function like split() in your programming language (e.g., Python: data.split(",")) to split the string variable containing comma-separated IDs into an array of individual IDs.
2. Loop through the array: Iterate through the array using a loop (e.g., for loop) and build your SOQL query by adding each ID within single quotes and separated by commas within parenthesis in the IN clause.

Example (assuming your programming language is Python):

data = "ID1, ID2, ID3"  # Your comma-separated string
# Split the string into an array
id_list = data.split(",")
# Build the SOQL query with a loop
soql_query = f"SELECT * FROM ObjectName WHERE Id IN ('{','.join(id_list)}')"
# Execute the SOQL query with your desired logic

B. Use string manipulation to build the SOQL query directly:

1. Remove unnecessary quotes when writing the dataflow output to the variable. This ensures the string remains a plain comma-separated list.
2. Directly build the IN clause within the SOQL query string by concatenating the single quotes around each ID joined by commas and enclosed in parenthesis.

Example:

soql_query = f"SELECT * FROM ObjectName WHERE Id IN ({data})"

Note: In both approaches, make sure you have proper error handling to catch potential issues during the process.The issue you're facing is related to how SOQL handles strings used in the IN clause. Here's why the SOQL query doesn't accept your variable and how to fix it:

if the issue persists please provide more information and screen shots if possible.

Hope this helps. Do let us know if you any further queries.

Answer 2

To pull Salesforce data using SOQL (Salesforce Object Query Language) in Azure Data Factory (ADF) and passing a variable, you can follow these general steps:

Set up Salesforce Linked Service: First, you need to create a linked service in Azure Data Factory for Salesforce. This linked service allows ADF to connect to your Salesforce instance. You'll need to provide authentication credentials and other required information.

Create a Pipeline: In your Azure Data Factory instance, create a new pipeline or open an existing one where you want to pull Salesforce data.

Add a Web Activity: Inside your pipeline, add a Web Activity. This activity will be used to call the Salesforce REST API and execute your SOQL query.

Configure the Web Activity: Configure the Web Activity to perform an HTTP request to the Salesforce REST API. You'll need to specify the endpoint URL for the Salesforce REST API, which includes the SOQL query you want to execute. You can use dynamic content or expressions to pass variables into the URL.

Pass Variables to SOQL Query: To pass variables into your SOQL query, you can use parameters or dynamic content in the URL of the Web Activity. For example, if you have a date parameter that you want to use in your query, you can include it in the URL like this: /services/data/vXX.X/query?q=SELECT+Id+FROM+Account+WHERE+CreatedDate>${pipeline().parameters.startDate}. Here, pipeline().parameters.startDate is a dynamic expression that retrieves the value of the startDate parameter defined in your pipeline.

Handle Pagination (Optional): If your query returns a large amount of data, Salesforce may paginate the results. You'll need to handle pagination by making additional requests to fetch all pages of data. You can do this by examining the response headers for a nextRecordsUrl and making subsequent requests until all data is retrieved.

Handle Authentication: Ensure that your Web Activity includes the necessary authentication headers to authenticate with Salesforce. This typically involves providing a Salesforce OAuth token or username/password credentials in the request headers.

Execute the Pipeline: Once you have configured the Web Activity, save your pipeline and execute it to pull data from Salesforce using your SOQL query with the variable.

By following these steps, you can pull Salesforce data using SOQL in Azure Data Factory and pass variables to customize your query. Make sure to test your pipeline thoroughly to ensure that it retrieves the data you expect and handles any potential errors or issues.