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

brnyh 1 Reputation point

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:
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:
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?


Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,455 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee

    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.

    0 comments No comments

  2. brnyh 1 Reputation point

    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...