0

I have following issue with string interpolation in a data flow expression builder.

I am reading values from a File via LookUp Activity in Azure Data Pipeline, I set a variable PRCTR to the value ('P00610','P00612') from a file and then assign this variable to a Data Flow Parameter.

When I start the data pipeline, my data flow gets this value from the variable: "prctr": "'('P00610','P00612')'" and the SQL source Query I write looks like:

"SELECT VALUE t FROM c.Metadata WHERE t IN {$prctr}"

I also tried adding single quotes to the variable:

"SELECT VALUE t FROM c.Metadata WHERE t IN '{$prctr}'"

In both of the cases my dataflow is failing with error:

Job failed due to reason: com.microsoft.dataflow.Issues: DF-DSL-002 - Parameter stream  has parsing errors. Parameter(s) with errors: prctr. Not honoring the datatype of parameter(s) could be one of the causes.

I do not mark this parameter as Expression:

My data pipeline where I read a file and set Variables. After that I assign variables to the data flow parameters.

How should I handle such string to process it correctly? By the way when I try to pass only 1 value for PRCTR and change the query to this:

"SELECT VALUE t FROM c.Metadata WHERE t = '{$prctr}'"

it works!

So smth with a string value where multiple items exist ('P00610','P00612') is causing an error.

Please share your ideas, thank you.

Improve this question
4
  • Are you sure that's string interpolation and not a parameterized query? If {$prctr} is treated as a parameter, you can't pass multiple values, no matter what. Everything will be treated as a single string value. Adding quotes won't help. There's no difference between IN 'banana' and IN '(banana). In both cases you have a single string value. IF interpolation is allowed, the string value you need to pass is ('P00610','P00612') - no outer quotes Commented Oct 18, 2024 at 15:41
  • If {$prctr} is treated as a query parameter the actual query sent to the database will be WHERE t = ? or Where t=@param1 with the actual value passed outside the query itself. Even if you use WHERE t IN (@param1) it's still a single value Commented Oct 18, 2024 at 15:44
  • As I am using parameters (assigned to data pipeline variables) as embedded expressions in the string query directly, I call it interpolation. I know that "('P00610','P00612')" is handled as a value, but I want it to be correctly added into the query so that the SQL syntax will be valid. Commented Oct 18, 2024 at 15:51
  • As I mentioned before, changing the parameter value from ('P00610','P00612') to P00610 makes the statement work when you also replace "IN" with "=". So some issue is probably has to do with single quotes I have here ('P00610','P00612') . Is there any way to see, which value is finally appearing in the SQL query within a DataFlow expression builder, I mean how exactly the final query looks like? Commented Oct 18, 2024 at 15:53

1 Answer 1

Reset to default
0

It looks like I found a solution using this kind of expression: "SELECT VALUE t FROM c.Metadata WHERE t IN ({split($prctr, ',')})". I also had to adapt the value I am reading from the file, changed it from ('P00610','P00612') into P00610,P00612. So hope this would help someone in the similar case.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

So after all, it's not string interpolation, it's a parameter. Instead of this workaround, consider importing the list of parameters into a separate table and joining the tables. This workaround won't work for more than a couple of values. Never mind handling numbers with thousand or decimal separators

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.