Azure data factory - custom mapping for Rest service

Question

So, I am creating a Copy activity that reads from SQL Server table and have to send the data to an API end point with the PATCH request. API provider specified that the body must be in the form of

"updates":[{"key1":"value1","key2":"value2","key3":"value3" }, 
            {"key1":"value1","key2":"value2","key3":"value3" }, ... 
            .... {"key1":"value1","key2":"value2","key3":"value3" }]

However, my sql table maps to json this way (without the wrapper 'updates:')

[{"key1":"value1","key2":"value2","key3":"value3" }, 
            {"key1":"value1","key2":"value2","key3":"value3" }, ... 
            .... {"key1":"value1","key2":"value2","key3":"value3" }]

I use the copy activity with the sink data set being of type Rest ..

How can we modify the mapping, so that schema gets wrapped by "updates" object ?

Answer 1

Using copy data activity, there might not be any possibility to wrap the data (array of objects) to an updates key.

• To do this, I have used a lookup activity to get the data, set variable activity to wrap the data with an updates object key and finally, use Web activity with PATCH method and above variable value as body to complete the activity.

• The following is the sample data I have taken for my SQL server table.

• Use look up activity to select the data from this table using table or query option (I used query option). The debug output would be as follows:

NOTE: If your data is not same as in sample table I have taken, try using the query option so the output would be something as shown below

• In the set variable activity, I have used an array variable and used the following dynamic content to wrap the above array of objects with updates key.

@array(json(concat('{"updates":',string(activity('Lookup1').output.value),'}')))

• Now in the Web activity, choose all the necessary settings (PATCH method, authorizations, headers, URL, etc.,) and give the body as follows (I used a fake REST api as a demo):

@variables('tp')[0]

• Since I am using the Fake REST API, the activity succeeds, but checking the Web activity debug input shows what is the body that is being passed to the Rest API. The following is an image for reference: