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

brnyh 1 Reputation point
2020-12-04T12:03:57.937+00:00

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?

45234-image.png

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

45283-image.png

45253-image.png

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!

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
    2020-12-07T22:57:42.887+00:00

    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 .

    45942-1207-1.png

    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 :

    45894-1207-2.png

    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
    2020-12-08T10:54:55.183+00:00

    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:
    46180-image.png

    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:
    46157-image.png

    But the input of this activity in the pipeline is now this:
    46205-image.png

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