Issues with ADF when importing/copying data from Sharepoint Online List skipping a few columns when mapping schemas

Felipe Regis E Silva 46

Dear community,

I'd like to ask some help to solve an issue involving importing data from a Sharepoint list using ADF. First I configured linked service and when configuring datasets to the right table I hit preview data, it ran fine but then I noticed I'm not able to see a few columns from the source as needed. I mean, ADF is able to query a "select * from" but it's not able to show all columns from source when I hit preview, it's my first observation.

When searching on the web for similar issues I found the following post: azure-data-factory-import-lookup-fields-with-optio.html
The author said there's an issue when sharepoint list field is a multiple value. He said ADF works when importing data from a single text field column.

In ADF, when clicking on mapping in copy activity and when importing schemas, ADF is not able to identity a few columns from source to complete import schemas.

I used documentation from connector-sharepoint-online-list as a reference guide and there is a topic about "Data type mapping for SharePoint Online List" which might be the reason why ADF cannot map schema from source correctly but I'm not sure. There is an OData data type I'm not familiar with.

I also found further documentation about mapping fields but it's not conclusive on how to fix the issue. how-external-data-source-is-mapped-to-sharepoint-list

Does anybody know anything that can help or know what is wrong? Is this a bug? I'm not sure if there's another solution I could use to import all columns I need from SharePoint list to Azure SQL using ADF.

Thanks in advance,

Regards,

Felipe Regis

HarithaMaddi-MSFT 10,126 Reputation points

2021-01-27T11:36:55.897+00:00

Hi @Felipe Regis E Silva ,

Welcome to Microsoft Q&A Platform. Thanks for posting the query.

In order to investigate further, please share pipeline run id by running pipeline with the source columns that are partially detected which will help our team to access logs and will share more insights on the issue.

Looking forward for your response!
Felipe Regis E Silva 46 Reputation points

2021-01-27T12:08:06.907+00:00

Thanks @HarithaMaddi-MSFT , it follows the pipeline run id when I was debugging and doing some tests.
Pipeline run id: 35b5d1c4-1c1b-49b4-9e9b-b51d87567934
Let me know if more details are needed in order to help with this issue.
HarithaMaddi-MSFT 10,126 Reputation points

2021-01-28T05:18:43.88+00:00

Thanks @Felipe Regis E Silva for sharing the details. I will share the same with the internal teams for further investigation and get back to you once I hear from them.

Stay tuned!
HarithaMaddi-MSFT 10,126 Reputation points

2021-01-29T06:39:57.473+00:00

Thanks @Felipe Regis E Silva for your patience. Product team confirmed that query is odata query, not sql query and suggested to try with table and let us know if all columns are showing up. Also, refer this document for additional details.

If the columns are still missing, could you please list the columns (including type) that are missing? Please note that the complex types (link, person, group, etc) are not supported.

Looking forward for your response!
Felipe Regis E Silva 46 Reputation points

2021-01-29T11:57:31.55+00:00

Hi @HarithaMaddi-MSFT , I tried with table without success and columns are still missing from the Sharepoint source. About listing data types from missing fields on the source side, can you help me on how can I do it on Sharepoint end once they're not showing in ADF?

Pipeline run was completed with success but it's still missing a few needed columns from source list as I described initially. It follows print with details from trial below. As you mentioned, It might be sth involving complex types from the source and I asked some updates to ADF team as well meanwhile we'll keep using traditional data importing paths such as SSIS packages to work with Sharepoint sources.

Thank you for your attention and help.
HarithaMaddi-MSFT 10,126 Reputation points

2021-02-01T12:09:49.61+00:00

Thanks @Felipe Regis E Silva for sharing more details. I will check with product team and come back with you once they share any more insights on the same.
HarithaMaddi-MSFT 10,126 Reputation points

2021-02-02T10:05:22.293+00:00

Thanks @Felipe Regis E Silva for posting the query. Product team suggested to check in list setting page and to create sample list to figure out the data types in SharePoint list. Later, they can be compared with compatible datatypes in this document.

Please let us know the details once you are able to check the type of the missing columns.
HarithaMaddi-MSFT 10,126 Reputation points

2021-02-03T07:28:36.897+00:00

Hi @Felipe Regis E Silva ,

We have not received a response from you. Are you still facing the issue? If you found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.
Felipe Regis E Silva 46 Reputation points

2021-02-03T16:12:04.413+00:00

Hi @HarithaMaddi-MSFT , sorry for later response and thanks for the fup. I'm working with teams on another urgent issues. I'm plannig to do some tests today or tomorrow using details you provided and I'll keep you and community up-to-date. Thanks
Toyin Olape 1 Reputation point

2022-02-21T07:09:10.903+00:00

@HarithaMaddi-MSFT Please, I've run into the same issue and was wondering if there was a resolution from Microsoft ?

@Felipe Regis E Silva Did you ever resolve the issue were facing here? Please share the solution if you were able to find the solution. Thank you
russellgove 1 Reputation point

2022-10-21T22:10:04.543+00:00

Hi,
I'm having the same issue. Anyone find any workarounds?
RG

1 answer

Anonymous

2023-08-23T10:26:45.58+00:00

I am facing same issue

does above problem have any solution?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment