ADF Copy Activity Copying Only 1 Row

Question

Team,

I have an ADF copy activity.

Purpose: Query Log Analytics Workspace > Write Results to Azure SQL DB

Problem: Only 1 row being written to SQL DB.

1

Copy activity Settings

2

Content-Type below. Request body with KQL query. No Pagination rules added

3

Details of Base and Relative URLs

4

Source Preview successfully shows KQL query results. Only UPN result is expected from the query in the Request Body

5

Mapping from Source to Destination; selecting only the required field

6

Copy activity completes without error

7

SQL DB receives just 1 row

Any assistance is greatly appreciated.

Answer 1

Hi Team, here is the solution for my case:

Please note that the [0] refers to the position in the JSON. So if I add more fields to my KQL results, I would need to map [1], [2], etc.

Answer 2

The solution was to Map complex values to String > Change the Collection Reference to $[‘tables’][0][‘rows’] > Rows became an Array [] > then map the json field [0] to the UPN in SQL column.

This is now resolved for me.

Answer 3

Hi Lolas-4729,

Glad that you figured out the way to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to "Accept " the answer.

Complex values needs to be mapped to String . To achieve it , we need to Change the Collection Reference to $[‘tables’][0][‘rows’] and then map the Json field with index 0 to the user principal name in SQL column.

Kindly accept the answer by clicking on Accept answer button so that the solution reaches the community in case anyone is facing similar issue. Thankyou.

Answer 4

Hi Lolas-4729

Thanks for using MS Q&A Portal and for your question. I think all steps you mentioned are ok, except step 5. As highlighted below, we are just mentioning the copy activity to bring just one row (rows[0][0])

What you can try?

Please remove the indexing part from rows and just mention ['rows'] and see if that fixes the issue.

Let us know how it goes

Answer 5

Good morning. I tried this. See below:

I tried with $['rows'] - Copy activity completed but no value(s) is inserted into SQL table.

I then tried just ['rows'] and ['rows'][0]; in both of these cases the Copy Activity fails.

Details: ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JArray is not supported yet. Please either remove the targeted column 'userPrincipalName' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common,'