Azure Data Factory Web Post output to CSV

Question

Azure Data Factory Web Post output to CSV

Dondapati, Navin 291

Hi Guys,

We have web Activity uses url and post method , with multiple select statement, we want output of post activity to be loaded to csv file, is it possible with ADF?

Rest Connection and Blob connection are sucessful.

we used copy activity rest-->blob; got below error
{
"errorCode": "2200",
"message": "Failure happened on 'Source' side. ErrorCode=UserErrorHttpStatusCodeIndicatingFailure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The HttpStatusCode 415 indicates failure. ,Source=Microsoft.DataTransfer.ClientLibrary,'",
"failureType": "UserError",
"target": "Copy data1",
"details": []
}

Regards,
Navin

Accepted answer

1 additional answer

Your answer

Answer 1

MartinJaffer-MSFT 26,236

I have made a solution, by taking the JSON you provided, and uploading to a public blob container.
Also uploaded, a csv / txt file whose contents are a newline.

Here I take advantage of the "Additional Columns" feature of the Copy Activity. The additional columns appends values to the end of each row read from the source. Since I need a row to append to, I uploaded a file, empty except for a newline. This I use for my source. If you want to include headers, a second row is needed.

For the sink I also used a blob, writing to csv. The output looks like

   Prop_0,Code,Qualifier  
   ,"686","AMA"

MartinJaffer-MSFT 26,236 Reputation points

2020-07-30T21:58:53.473+00:00

@Anonymous did the last answer solve your issue? If so, please mark as answered.
If not, could you let me know whether you found your own solution, or are still in need of help?
Rama Gudepu 1 Reputation point

2020-08-18T09:42:29.323+00:00

Hi,

Thanks for your response @MartinJaffer-MSFT , I have a quick question. what if there are many columns in the web activity output JSON, should i create additional columns for every single column in JSON or is there any way i can pick up all the data in the JSON as source and map it to sink? Thanks

Answer 2

MartinJaffer-MSFT 26,236

Hello @Anonymous and thank you for your question.

If I understand your situation correctly, you want to pull data from an URL and write it to csv.
First you tried with the REST connector in Copy activity , but got the error with HttpStatusCode 415. The "test connection" button worked for both. What happened when you tried "preview data"?

Then you tried using a Web activity, and were able to successfully read the data, no errors, but do not know how to write it.

The HttpStatusCode 415 is "Unsupported Media Type". This means the server does not accept the requested format of data. I.E. you tell the server "Give me the results in AVRO format" but the server says "No, I only do CSV and text, not AVRO".

I do not know what format the data you retrieve is. The REST connector only supports JSON. This could be the reason.
I would like you to try the HTTP connector instead. The HTTP connector supports many data formats. I think this should help.

Using the HTTP connector will be easier than trying to write the output of a Web activity. If the HTTP connector does not work, let me know and I will try to help with the Web activity.

Dondapati, Navin 291 Reputation points

2020-07-21T20:12:43.863+00:00
I tried even with Json as out put file; and tried with Copy data wizard as well, when i preview data got same error httpStatusCode 415 at source itself ; while we get JSon data while using PostMan app,

Data return in Json format as well but more like below:

{ "taste": [ { "Code": "686", "Qualifier": "AMA", } ] }

When i use WEB activity, i get results with out issue.
MartinJaffer-MSFT 26,236 Reputation points

2020-07-22T21:09:12.357+00:00
@Anonymous

There are a couple more things I'd like to check. Are you passing any 'Additional Headers' ?
Specifically Content-Type or Content-Encoding

meanwhile I will do 2 things:

Inquire internally about the differences between REST, HTTP, and web activity

Work on providing you a work-around to write after Web activity. I will work under the assumption, the sample data return your provided, is typical, and the entirety (1 record returned per web activity).

Share via

Azure Data Factory Web Post output to CSV

1 additional answer

Your answer