Keep quotation marks in request body when copy data from API in Azure Data Factory

Question

I need to call an API in copy data activity and place it in a CSV-file. I need the request body to be dynamic, because i will call the API for different filters. I retrieve the filter-value from a Lookup activity from a sqldb-dataset. The part of the request body that is dynamic is this:
<Filter>[List]=',activity('Lookup1').output.value.codelist,'</Filter>
The problem is that the request body requires the value of the list to be placed within quotation marks, i.e look something like this:
<Filter>[List]="536,323,235,674"</Filter>
The problem is that i cannot add these quotation marks in the lookup-query, without the output returning backslashes. If i try to pass the output from lookup without quotation marks, and add them in the request body, backslashes is added there aswell. I need the request body to NOT add backslashes to the quotation marks around the list value. How can i do this?

If i try to add quotation marks to the lookup-query, the output still comes with backslashes (even if the preview data does not)

Preview data snipped

So i think i need to do this in the request body somehow, but i am not sure how. I've tried with different solutions, like replace and substring functions, but it still adds the backslashes to each quotation mark when it runs, and so my API is not called.

How can i fix this?

Thanks!

Answer 1

Hello @brnyh ,

Thanks for the ask and using the forum .

Since you mentioned that " I retrieve the filter-value from a Lookup activity from a sqldb-dataset" , i think we can fix it on the SQL side.

I tried to repro

create table test1000
(
columanname varchar(1000)

)

INSERT INTO test1000 values('"210004,2000,4000"')
select columanname
from test1000

I am getting the output you called out what you called out .

Now in the Lookup move to query ( i am assuming that you were using the tablke before ) with something like .

select substring(columanname,2,(len(columanname)-2)) as'columanname'
from test1000

Output now is :

Let me know if this helps .

Thanks Himanshu
Please do consider to click on "Accept Answer" and "Upvote" on the post that helps you, as it can be beneficial to other community members.

Answer 2

Thank you, @HimanshuSinha-msft
However, i might have been a bit unclear in my question. I am also able to get my lookup output to return inside question marks. Im using query in a lookup with the following query:
'Declare @val Varchar(MAX);
Select top(10) @val = COALESCE(@val + ',' + Code,Code)
From Table1
order by Code asc
Declare @codelist Varchar(MAX)
SET @Codelist = '"' + @val + '"'
select substring(@codelist,2,(len(@codelist)-2)) as 'codelist''

This returns the output inside question marks like this:

In this case however, the question marks are skipped entirely in the input of the copy data activity.
The part of my dynamic request body looks like this:

But the input of this activity in the pipeline is now this:

So i believe i have to solve this in the dynamic request body somehow...