Connecting to the Office 365 Reporting ODATA feed in SSIS

[Raymond Boone]

I've been trying to connect to the ODATA feed located here https://reports.office.com/pbi/v1.0/<tenantid> with SSIS. I can connect Excel 2016 and Power BI to it and consume data. SSIS looks like it will connect and it even gets as far as listing collections, but then when I click Preview, I get the following error:

Unable to cast object of type 'Microsoft.Data.DataFeedClient.DataFeedODataEdmWrapper' to type 'Microsoft.Data.DataFeedClient.DataFeedODataEdmWrapperV3'. (Microsoft.Data.DataFeedClient)

That's as far as I can get--can't get to the columns and data contained in the feed.

I'm targeting SQL Server 2016 (and have tried 2017) as well with SSDT for Visual Studio 2017 version 15.4.5.

Has anyone had any success connecting to this data source in SSIS?

[#2]

I have exact same issue..Seems Microsoft not allow to grab data from office 365 to local..

[#3]

Welp it's still an issue a year later. I can still connect in Power Query (in Power BI or Excel) but not via SSDT--whether it's an SSIS package or a tabular data model. Even the new Power Query source for SSIS can't do much past make a connection. It errors out when you actually try to use the connection for anything.

[#4]

@Raymond BooneHere we are almost another year later. Did you ever get this working?

[#5]

@Jill Kaszynski I just gave up and moved on.

[#6]

There's probably some other way to query 365 to get the info--maybe it's Office Graph. I just wish someone (from Microsoft) had chimed in to tell me that.

[#7]

I'm looking for help in this area too. I can't connect to the Office 365 data set in local Power BI desktop, my account is connected and I can access online. When I do the get data for the OData feed, I get a "datasource.error: Odata: A support MIME type could not be found that matches the content type of the response. ....." Wondering if there are additional permissions that need to be granted to allow the connecting Odata from local. Any one from Microsoft can respond. Seems like others on this thread have given up.