ADF load data to Rest API Sink

Arundhati Sen 151 Reputation points
2023-03-23T18:02:43.38+00:00

My data is present in Azure SQL Database table, and I have to PUT the data in Blackline API in text format, using ADF

Blackline API example : https://developer.blackline.com/docs/data-ingestion-accounts-api/1/routes/dataingestion/accounts/put

URL : https://{environment}.api.blackline.com/dataingestion/accounts

Content-Type : "text/plain"

example body for PUT method:

"3000 Account-DI-02 AS020 1102 BK 12111 Test Bank Account Cash A Asset TRUE TRUE GBP USD 6/30/2014 34664.86 23058.37 34664.86 IMPCash 2 IMPCash 3 IMPCash 4 IMPCash 5 IMPCash 6"

So without the header the values only I have to send to an API using PUT method.

  1. Is it achievable using ADF copy activity, where I use SQL DB as source and RestApi connector as Sink with PUT method? But how can I send the request as plain text? OR
  2. Is it achievable using ADF web activity? Can I do a lookup from table w/o header and send the output as text in web activity request Body? But here formatting issue can come is the string

Please suggest if this is doable with ADF or help me with some alternate solution.

Azure SQL Database
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,478 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. MartinJaffer-MSFT 26,011 Reputation points
    2023-03-24T16:51:41.7666667+00:00

    @Arundhati Sen Hello and welcome to Microsoft Q&A.

    I understand you need to move data from a SQL DB (on-prem or azure?) to a REST endpoint which requires plaintext request body and returns JSON response body.

    REST activity always uses JSON format in source and sink (or xml as source in specific cases). So, no I don't think you can use Data Factory REST connector.

    You CAN use Web activity. That does work. Web activity accepts any text-based data. Binary gets tricky.

    However you have to be aware of the result size limits of Lookup Activity. It only returns the first N results or 4MB. So Lookup is not always a viable solution.

    An easier solution might be Logic App.

    As your API returns JSON response body, I wonder if it can also accept JSON requests... Some API's can accept multiple formats.

  2. Arundhati Sen 151 Reputation points
    2023-03-29T06:04:45.1033333+00:00

    Currently I am able to post one row at a time using lookup+web activity,

    Suppose lookup activity is giving output 3 rows

    Output of lookup activity:
    User's image

    Then under For-Each loop I have web activity and in body I am passing the lookup output : 

    @{item().id}	@{item().col1}	@{item().col2}	@{item().name}	@{item().col5}

    which is sending the input in plain/text format

    "body": "C0004\t1000\t\tAT020\t1102"

    Input of web activity:
    User's image

    But it is not feasible as this can only send one row at a time, and to send 1000 rows, I have to loop through 1000 times.

    Can someone please suggest some other method using ADF.