SSIS and OData - Connecting to CRM Online

(0) Share

Report

Posted on by Lucas Cavalcante

I'm using Visual Studio 2015 Enterprise Update 1 with SSDT installed.

I created a Analysis Services project and was able to connect to several OData Feeds, including Sharepoint Online. However, I could not connect to CRM Online OData service.

The OData Source Editor on Visual Studio says "Cannot open this connection". Strangely enough, when I test the Connection, it succeeds, even with wrong credentials.

Does anyone is experiencing this? It is a problem with Dynamics CRM Online? Microsoft Online Services Authenticator?

There is any difference between authenticating on Sharepoint Online and Dynamics CRM Online?

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (0)

Royal King 27,686 on at

Like (0)

Report

you can't use odata service outside the crm to access the data in crm 2015 ( Except mobile devices) . If you have crm 2016 you could use web API to access the data in SSIS. In order to connect to CRM in crm 2015 you have to write script component where script component will use Organization service API to interact with CRM .

Was this reply helpful? Yes No
Lucas Cavalcante on at

Like (0)

Report

What is the difference between a mobile device and any other kind of device from the Web Server standpoint of view? There isn't. If I can't use OData outside the CRM, why I'm using it right now with an standard web browser?

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

OData API requires OAuth authentication that's the reason it works only in mobile devices and SSIS Odata source will not handle the OData authentication. OData is useful to build custom html5 based pages with in crm like editable grid .

Was this reply helpful? Yes No
Lucas Cavalcante on at

Like (0)

Report

Yes, the problem is with OAuth indeed. But isn't the "Microsoft Online Services Authenticator" bool to enable oauth handling? Can't understand why SSIS can't handle OAuth properly.

Neither Sharepoint Online or Project Online are currently using OAuth? Becausing it is working fine on both cases.

Was this reply helpful? Yes No
Royal King 27,686 on at

Like (0)

Report

Simple answer is you can't use crm ODATA in SSIS and thats the limitation. You have to use script component with organization SOAP API to interact with CRM in SSIS.

Was this reply helpful? Yes No
Lucas Cavalcante on at

Like (0)

Report

OData is a Protocol and SSIS can handle it. Does not matter if is from CRM, PM, CMS or whatever product implements it. SSIS does not really care.

Also, it is not a problem with OData itself, but by the means of authentication that CRM are using to allow access to this data: OAuth2.

What surprises me is the two other products that got that working very smoothly with SSIS (SPO and PO). Isn't all these services authenticated by Microsoft Online Service Authetication uzing the AAD?

There is any work around the limitation of OAuth2 on SSIS?

Was this reply helpful? Yes No
Aileen Gusni 44,524 on at

Like (0)

Report

Hi Lucas,

Yup you are right Odata, SSIS can handle it.

But when connect to CRM it doesn't do the OAuth by itself.

You can try another connector

stackoverflow.com/.../ssis-connecting-to-crm-odata-endpoint-dtd-error

Thanks

Was this reply helpful? Yes No