Linked Service - SharePoint Online Lists - Missing Nested Values

Question

Linked Service - SharePoint Online Lists - Missing Nested Values

Richards, Sam (DG-STL-HQ) 151

I am using the SharePoint Online list linked service in Azure Synapse and am running into a problem regarding nested values.

Using the preview data feature, I am able to see the desired SharePoint online list as expected; however, colums with nested values are only being shown as the Id value. This id is part of a nested list which has a set of values, the first of which is Id (the unique identifier). However, no others are present, specifically names associated with that Id.

Indeed, when I copy the SharePoint list to a .json in my Datalake, I see only the Id field.

However, this is actually a nested value as can be seen when I view the file in Power BI Power Query:

Finally, during the copy data activity from SharePointOnline list to .json, there does not seem to be any options for "nested" or "complex" data types that I would expect.

I have tried to use both Preserve and Flatten Hierarchy options in the .json Output, but have no success.

Are there any options available for nested lists in Azure Synapse? I would really like to avoid using Power Automate or Power Query as I would like this file to be available to others in my organization that utilize Azure.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-09-15T22:26:46.71+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Thanks for the question and using MS Q&A platform.

As per my understanding, your source SharePoint online list has nested/complex JSON structure, which are missing while trying to copy them using ADF. Please correct me if I'm not clear.

While doing data preview from Sharepoint dataset, were you able to see the complete JSON structure or the nested values are missing in data preview as well? Appreciate if you could share the actual json structure where the data is missing. Feel free to upload sample data file by removing any sensitive information.

In case if you are data previewing is having all the columns and values and only missing while copying then, then one workaround is to copy the Sharepoint list data (JSON) to a blob as is without using mapping and then use a mapping dataflow activity to flatten the nested/complex json structure into simple structure. Copy activity is suitable for complex/nested JSON files.

Do let us know if this is helpful else, please share few additional screenshots and sample file so that we can better understand and assist accordingly.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-25T21:12:00.86+00:00

Any updates on this @KranthiPakala-MSFT ?
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-25T21:24:35.74+00:00

@KranthiPakala-MSFT thanks for the response.

When I preview the data using the following settings:

The only options I see on the SharePoint list preview is shown below (note the project manager, project accountant, and responsible partner fields only come in as the unique ID at the top of the nested value.

Below is an example of what should be available in the project manager field (note this applies to all the named fields listed above including Project Accountant and Responsible Partner):
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-09-29T01:20:40.273+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Checking to see if you have got a chance to send an email as per my instructions in my previous private message. Please do confirm here once email is sent.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-29T13:33:21.24+00:00

I did @KranthiPakala-MSFT I will follow up with the requested data in the next few hours.
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-29T15:49:51.697+00:00

@KranthiPakala-MSFT I just sent the email with the requested data.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-10-04T00:29:47.443+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Thank you for sharing the details over email. After reviewing the details, we have enabled one-time free support for your subscription for deeper investigation of this issue. Please do follow the steps provided to file a support ticket and share the Support ticket number here for tracking.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-10-04T13:06:57.65+00:00

I responded in email but the link you provided does not show any options for free technical support @KranthiPakala-MSFT
Anonymous

2023-11-06T14:54:43.0133333+00:00

Hi Sam, I am having the same issue with previewing data from a sharepoint reader, the difference is I don't even have the ids for the PO and ScrumMaster (in my case), so those columns are missing completely. What do you suspect as an issue? Thank you!
Richards, Sam (DG-STL-HQ) 151 Reputation points

2023-11-06T15:03:10.6333333+00:00

The only way I was able to fix the issue was to use a spark notebook to do the parsing of data in pyspark. Crazy that the SharePoint connector does not support nested datatypes.
Anonymous

2023-11-08T09:43:56.3866667+00:00

Thank you for your reply! How did you use pyspark to get the object given the id? For example, given the ProjectManagerId how did you get the object for ProjectManager?
Richards, Sam (DG-STL-HQ) 151 Reputation points

2023-11-08T13:52:49.8833333+00:00

Treat it as a nested JSON object in pyspark then parse it as you would normally do for a JSON.

This might help you: https://sparkbyexamples.com/pyspark/pyspark-json-functions-with-examples/

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-09-15T22:26:46.71+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Thanks for the question and using MS Q&A platform.

As per my understanding, your source SharePoint online list has nested/complex JSON structure, which are missing while trying to copy them using ADF. Please correct me if I'm not clear.

While doing data preview from Sharepoint dataset, were you able to see the complete JSON structure or the nested values are missing in data preview as well? Appreciate if you could share the actual json structure where the data is missing. Feel free to upload sample data file by removing any sensitive information.

In case if you are data previewing is having all the columns and values and only missing while copying then, then one workaround is to copy the Sharepoint list data (JSON) to a blob as is without using mapping and then use a mapping dataflow activity to flatten the nested/complex json structure into simple structure. Copy activity is suitable for complex/nested JSON files.

Do let us know if this is helpful else, please share few additional screenshots and sample file so that we can better understand and assist accordingly.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-25T21:12:00.86+00:00

Any updates on this @KranthiPakala-MSFT ?
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-25T21:24:35.74+00:00

@KranthiPakala-MSFT thanks for the response.

When I preview the data using the following settings:

The only options I see on the SharePoint list preview is shown below (note the project manager, project accountant, and responsible partner fields only come in as the unique ID at the top of the nested value.

Below is an example of what should be available in the project manager field (note this applies to all the named fields listed above including Project Accountant and Responsible Partner):
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-09-29T01:20:40.273+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Checking to see if you have got a chance to send an email as per my instructions in my previous private message. Please do confirm here once email is sent.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-29T13:33:21.24+00:00

I did @KranthiPakala-MSFT I will follow up with the requested data in the next few hours.
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-09-29T15:49:51.697+00:00

@KranthiPakala-MSFT I just sent the email with the requested data.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-10-04T00:29:47.443+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Thank you for sharing the details over email. After reviewing the details, we have enabled one-time free support for your subscription for deeper investigation of this issue. Please do follow the steps provided to file a support ticket and share the Support ticket number here for tracking.

Thank you
Richards, Sam (DG-STL-HQ) 151 Reputation points

2022-10-04T13:06:57.65+00:00

I responded in email but the link you provided does not show any options for free technical support @KranthiPakala-MSFT
Anonymous

2023-11-06T14:54:43.0133333+00:00

Hi Sam, I am having the same issue with previewing data from a sharepoint reader, the difference is I don't even have the ids for the PO and ScrumMaster (in my case), so those columns are missing completely. What do you suspect as an issue? Thank you!
Richards, Sam (DG-STL-HQ) 151 Reputation points

2023-11-06T15:03:10.6333333+00:00

The only way I was able to fix the issue was to use a spark notebook to do the parsing of data in pyspark. Crazy that the SharePoint connector does not support nested datatypes.
Anonymous

2023-11-08T09:43:56.3866667+00:00

Thank you for your reply! How did you use pyspark to get the object given the id? For example, given the ProjectManagerId how did you get the object for ProjectManager?
Richards, Sam (DG-STL-HQ) 151 Reputation points

2023-11-08T13:52:49.8833333+00:00

Treat it as a nested JSON object in pyspark then parse it as you would normally do for a JSON.

This might help you: https://sparkbyexamples.com/pyspark/pyspark-json-functions-with-examples/

Answer 1

Richards, Sam (DG-STL-HQ) 151

Just got feedback from an Azure Data Factory engineer this nested values are not supported in SharePoint lists.

Hello Sam,

Greetings!

After checking with my internal team. We would like to inform you that Sharepoint does not support Data flow it’s only supports Copy activity and Lookup activity. Please find the below documents and snapshots for your reference.

Copy data from SharePoint Online List - Azure Data Factory & Azure Synapse | Microsoft Learn

Connector overview - Azure Data Factory & Azure Synapse | Microsoft Learn

Thanks & Regards,
Amani Jallu.

Pretty disappointing that this is not available @KranthiPakala-MSFT . Hopefully it becomes a feature in the future but I will unfortunately have to add another step in the data extraction process using Power Automate to achieve my needs. One day Azure Synapse will be a one stop shop but not today :(.

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-10-19T06:42:01.927+00:00

Hello @Richards, Sam (DG-STL-HQ) ,

Appreciate much for sharing your findings here from the support ticket analysis.
I noticed that you have already filed feedback in IDEAS forum - https://feedback.azure.com/d365community/idea/94179d4b-e14b-ed11-a81b-000d3ae545b3. Sharing it here as others can up-vote and comment on it to help increase the priority of the feedback.

Thank you

Share via

Linked Service - SharePoint Online Lists - Missing Nested Values

1 answer

Your answer