I want to use ADF to bulk update records with SQL as the source and a REST API as the sink. The API doesn't allow a bulk update with all data in a JSON format in a single request. My dataset is 2.5k / 30k records for either incremental or full loads.
I would have hoped to use the Copy Data Activity but I'm running into some issues. To update the records I would have to send a PUT request to https://api.interfaceurl.com/users/{user_id} with the userId in the url. However I can't seem to get this to work using the Copy Data Activity.
As a work around I've used a Lookup Activity followed by a For loop which contains a web activity performing the PUT request. It works but it feels sub optimal, especially as the Look-up activity has a row limit of 5000 records. Workaround
Is there a more efficient way to achieve what I want?
You can try ADF Dataflows for your scenario. As your URL requires the user_id
from the source, give the Base URL as https://api.interfaceurl.com/users/
in the REST linked service.
In the dataflow source, give your SQL database dataset. Convert your user_id
to string type using a derived column transformation and store in a new column as shown below.
Now, use External call transformation to call your REST API. In this transformation, give your REST API linked service with PUT method and set the above created column as Row relative URL.
To not include this extra column, check the Skip writing row relative URL option. You can give any JSON dataset for the sink.
For each user_id
in the source, the Relative URL from this column will be appended to the Base URL and REST API will be called with PUT method.
You can also go through this MS ask by @Ralph Jáquez to know more about this.