In an SSIS package, I'm trying to extract data from a SharePoint list. I'm using the OData source connection manager. I put in the proper Service document location. I'm using an Authentication Type: Windows Authentication. When I hit the 'Test Connection' button I get 'Test connection succeeded'.
In the OData Source Editor I choose the connection manager I just configured. I choose the 'Collection' option. I then choose the list from the collection. When I hit the 'Preview' button the following error pops-up:
An error was read from the payload. See the 'Error' property for more details. (Microsoft.Data.OData)
Here is the expanded error:
===================================
An error occured when reading the OData feed. (Microsoft.Data.DataFeedClient)
------------------------------
Program Location:
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.InterceptODataException[T](Func`1 function)
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.ReadNextODataEntry()
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.ReadNextRow(IRow reuseRow)
at Microsoft.Data.DataFeedClient.BufferedRowsReader.FetchNextRow(IRow reuseRow)
at Microsoft.Data.DataFeedClient.BufferedRowsReader.MoveNext()
at Microsoft.Data.DataFeedClient.DataFeedDataReader.InternalMoveNext()
at Microsoft.Data.DataFeedClient.DataFeedDataReader.Read()
at Microsoft.DataTransformationServices.DataFlowUI.ODataConnectionPage.PreviewButton_Click(Object sender, EventArgs e)
===================================
An error was read from the payload. See the 'Error' property for more details. (Microsoft.Data.OData)
------------------------------
Program Location:
at Microsoft.Data.OData.Atom.BufferingXmlReader.ReadNextAndCheckForInStreamError()
at Microsoft.Data.OData.Atom.BufferingXmlReader.ReadInternal(Boolean ignoreInStreamErrors)
at Microsoft.Data.OData.Atom.BufferingXmlReader.Read()
at System.Xml.XmlReader.SkipSubtree()
at System.Xml.XmlReader.Skip()
at Microsoft.Data.OData.Atom.ODataAtomEntryAndFeedDeserializer.ReadFeedContent(IODataAtomReaderFeedState feedState, Boolean isExpandedLinkContent)
at Microsoft.Data.OData.Atom.ODataAtomReader.ReadAtEntryEndImplementation()
at Microsoft.Data.OData.ODataReaderCore.ReadImplementation()
at Microsoft.Data.OData.ODataReaderCore.ReadSynchronously()
at Microsoft.Data.OData.ODataReaderCore.InterceptException[T](Func`1 action)
at Microsoft.Data.OData.ODataReaderCore.Read()
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.ReadNextODataEntryInternal()
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.<ReadNextODataEntry>b__5()
at Microsoft.Data.DataFeedClient.DataFeedODataReaderV3.InterceptODataException[T](Func`1 function)
It doesn't matter what collection I choose, same error. I have even tried to limit the data with a query option to 1 field I know that has no issues.
I'm using SSDT 2017. The SharePoint version is 2013.
Any ideas? Thanks in advance.
If you are facing problems when trying to connect to Sharepoint using ODATA components you can use SSIS Sharepoint List Adapter which are a project done by Microsoft SSIS community (product team).
These components was published on Codeplex for SSIS 2005 and 2008, then few years ago, the project was migrated to GitHub and updated to support SQL Server 2016,2017.
CodePlex project URL
GitHub project URL
Sharepoint List adapter 2017 release
Related question
While searching i found the following topic about using ODATA Source and Sharepoint:
In the comments there are a user (Ravikiran) that mentioned the same exception and it looks like that it was a data type issue:
For that i was able to solve it by asking the sharepoint administrator to create a calculated column which would refer to those lookup column and display the value. This calculated column will be of basic type varchar so its easy to pull the data for this through odata adapter.
Also another user (Angela) has given another solution:
Just to let you know that I got it working. I installed Sharepoint List Adapters which I added it to my SSIS Package