ADF - How to post CSV format data in the Rest API Body

Question

For Salesforce Bulk API Rest API, we need to post data in CSV format in the body. Our CSV file is stored in a Blob Storage, how can this file be referenced in the API body, so that data from the CSV file is posted to the API call.
In Web Activity we have a dataset setting, can this be leveraged to be used as source and its content posted in the body.

Answer 1

Hello @Riyaz Patanwala and welcome to Microsoft Q&A.

Unfortunately, the web activity feature you are pointing out does not do what you think it does. To my understanding, it takes the definition of the Linked Service / Dataset and adds it to the body. It does not let you put the actual data into the body. A very niche case in my opinion.

Instead, the method I use to pass data in web activity, is to first fetch the data using another web activity.
That is, first a web activity points to the blob you want to read. This fetches the data and makes it available in the activity output.
Then re-use the output in the body of the "Insert Data" web activity.
Below are details of how to set up the reading web activity for blob.

URL: https://myStorageAccount.blob.core.windows.net/myContainer/myFolder/myFile.txt
Headers: x-ms-version 2017-11-09
Advanced -> Authentication: Managed Identity
Advanced -> Authentication -> Resource: https://storage.azure.com/

By selecting Managed Identity authentication, you need to go to the storage account, and grant blob reader / blob contributor role to the Data Factory.

In the body of "Insert Data" use something like @activity('Get stuff').output.Response to pull in the results of the read.

Do keep in mind, the Web activity returns at most 4 MB of data. If your file is bigger, you may run into problems.