How to use set variable data in copy transformation for querying in where clause condition (DB-CosmosDB)

Question

Hello all ,

Here I am trying to get the data from lookup and setting it as a variable and when I try to use it in copy transformation I am getting error. adding the snapshots-

Answer 1

@Balaji D

Welcome to Microsoft Q&A platform and thanks for posting your query here.

The error message you're encountering, "Syntax error, incorrect syntax near '('.')", arises because you're attempting to directly embed the output of the Lookup activity (which is likely a JSON object) into your SQL query within the Copy activity. SQL doesn't interpret JSON directly in a WHERE clause. You need to extract the specific value you need from the Lookup output and use that in your query.

Get the Variable Value: In the Set Variable activity, ensure you're extracting the specific value you need from the Lookup output. Use an expression to get the value, not the entire JSON.

Example (assuming your Lookup returns a column named 'customer_id'):

@activity('Lookup1').output.firstRow.customer_id 

This expression accesses the firstRow of the Lookup output and retrieves the customer_id value. Adjust customer_id to the actual name of your column.

Parameterize the Copy Activity Query: Modify the source query in your Copy activity to use a parameter placeholder instead of directly embedding the variable.

Example SQL Query (using @customer_id_param as the parameter): SQL

SELECT * FROM YourTable WHERE customer_id = @customer_id_param

Map the Variable to the Parameter: In the Copy activity's Source settings, under "Query parameters", map your variable to the query parameter.

• Parameter Name: @customer_id_param (must match the name in your query)
• Value: @{variables('yourVariableName')} (replace yourVariableName with the actual name of your variable)

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you