I need to read some data from a SP list. I have the OData connection manager setup. When I test the connection to my sharepoint site, it says it's successful. I added in a data flow task and then an OData Source. In the OData Source editor, I select the Odata connection manager. I want to use a collection. However, it says "Unable to load collection for the connection Odata Source, reason: Cannot open this connection"

Anybody know how to resolve this?

• Edited by hellomynameiskayla Thursday, February 19, 2015 9:52 PM

What SSIS version, what SP? Why there is now OData if this SharePoint?

• Edited by hellomynameiskayla Friday, February 20, 2015 2:30 PM

Hi kayla,

Just as you said, the current SharePoint List Adapter that extracting and loading SharePoint Data in SQL Server Integration Services supports x86/x64 systems using SQL 2005 / SQL 2008 / SQL 2008 R2/ SQL 2012. It doesnt support SQL 2014. Maybe we need waiting the update.

The OData Source component can be used to read data from SharePoint lists in SSIS 2014. Please download and install the 64 bit ODataSourceForSQLServer2014-amd64.msi or 32 bit ODataSourceForSQLServer2014-x86.msi from the Microsoft SQL Server 2014 Feature Pack.

Reference:
Tutorial: Using the OData Source
Using the SSIS OData Source Connector to read data from SharePoint lists

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

If you have any feedback on our support, please click here.

Hi kayla,

Just as you said, the current SharePoint List Adapter that extracting and loading SharePoint Data in SQL Server Integration Services supports x86/x64 systems using SQL 2005 / SQL 2008 / SQL 2008 R2/ SQL 2012. It doesnt support SQL 2014. Maybe we need waiting the update.

The OData Source component can be used to read data from SharePoint lists in SSIS 2014. Please download and install the 64 bit ODataSourceForSQLServer2014-amd64.msi or 32 bit ODataSourceForSQLServer2014-x86.msi from the Microsoft SQL Server 2014 Feature Pack.

Reference:
Tutorial: Using the OData Source
Using the SSIS OData Source Connector to read data from SharePoint lists

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

If you have any feedback on our support, please click her

I need to read some data from a SP list. I have the OData connection manager setup. When I test the connection to my sharepoint site, it says it's successful. I added in a data flow task and then an OData Source. In the OData Source editor, I select the Odata connection manager. I want to use a collection. However, it says "Unable to load collection for the connection Odata Source, reason: Cannot open this connection"

Visual Studio  BIDS 2013. SharePoint 2010. Planning to migrate to 2013 this year. I tried to use the SP Data source/destination adapter, but learned that I can't with Visual Studio 2013. I'm really looking for any way to read data from a SP list and import it into a SQL database (SQL 2014). Haven't found anything online that has worked for me the way they say it should. Maybe it's version issues.

I downloaded and added the OData source to my SSIS package. The odata connection manager successfully connects to the sharepoint site. However, when I use the data source in the Odata Source within the Data Flow, it gives me the "cannot open this connection" in the Collection list within the Odata source editor. I'm stumped as to why the connection manager has a successful test connection, but the collections don't pull up. Does the list need some special permissions? I'm able to access it directly on the sharepoint site and add items (the odata connection manager is running with my windows credentials)

• Merged by Katherine XiongMicrosoft contingent staff, Moderator 1 hour 24 minutes ago duplicate

Hi kayla,

Based on my research, someone run into the similar issue. He finally figured out that the name of one of his sharepoint lists started with a number, and also contained choice columns. The name of the list in the catalog started with c_123ListName, and the choice element started with C_123ListNameStatus. Lower case c for the primary list resource, upper case C for the choice resource. Something wasnt resolving correctly internally. When he changed the list name to start with a letter, the connection worked without issue.

Reference:
http://whitepages.unlimitedviz.com/2014/03/using-the-odata-source-connector-with-sharepoint-online-authentication/

Thanks,
Katherine