This BCS Adapter for CRM 2011 is from the CRM team (It does all of the BCS work for you by interrogating the CRM metadata service).
Summary: SharePoint BCS -> Pre-built Adapter (.NET Assembly) -> CRM web services - > CRM database
Download from Microsoft
Read "Connecting to CRM Online 2011 with SharePoint 2010 Business Connectivity Services"
Run tool to generate the XML mapping (.BDCM)
This solution uses a BCS Connector – a .NET Assembly responsible for mapping external data into a form usable by SharePoint. This component is loaded and hosted within SharePoint 2010 and communicates with CRM via the CRM Proxy Service.
Option 2: SQL Server Filtered Views
CRM recommends that you *don't* read from the Tables, so they provide SQL Views for this purpose.
Summary: SharePoint BCS -> CRM database
Not available for hosted CRM
Security issues as you are exposing the view.
Filtered Views in Microsoft CRM provide access to the data available that supports providing picklist name and id values (lookup tables).
If you only want read-only for CRM on-premises data for SharePoint users, this solution is fine. You create the External Content Type directly against the Filtered Views in the CRM database.
- Figure: The result of "SELECT * FROM FilteredCtx_Project". Use Office SharePoint Designer to hook this up
Option 3: Web Services
CRM provides web services.
Summary: SharePoint BCS -> Code calling CRM web services - > CRM database
Needs lots of code and test work.
Needs to be deployed and published to the web server.
Less performance than SQL filter views directly #1
#1 Note: Performance could be improved by making the reads from the views and the writes through the web service
1. Use BCS in VS 2010
2. Write code that calls the CRM web services (that access the CRM data)
Option 4: Expose OData from CRM as RSS
The CRM 2011 OData Query Designer can be used to build queries to expose the data from CRM as RSS
50 records limit. Need to page through the results.
Possible issues with firewalls and proxies because it uses Integrated Security for authentication.
No easy way to consume
Note: You can really only call the OData endpoint from an application that already has an authentication cookie with the CRM server.
i.e. you can't impersonate and call it like you can the standard WCF endpoints
The first step is to expose the data:
1. Install http://crm2011odatatool.codeplex.com
2. Make a query
- Figure: Designing a query
3. See the data
- Figure: See the data - RSS source for xtc_countrySet
The second step (and the problem) is consuming the data
- Figure: BCS has no option to consume RSS data. Please Microsoft SharePoint Team, we need a new 'Data Source Type' = OData
In summary, CRM 2011 can expose OData, but SharePoint 2010 BCS doesn't consume OData.
The 3 options to consume the OData/RSS data:
Consume the OData by SQL Server, via TSQL ??? Then use BCS to call SQL Server.
Summary: SharePoint BCS -> DataSourceType: SQL Server -> OData- > CRM database
You would need to be crazy to go down this route http://www.vishalseth.com/post/2009/12/22/Call-a-webservice-from-TSQL-(Stored-Procedure)-using-MSXML.aspx
· Consume the OData by a BCS adapter + code calling web services (same story as above).
Summary: SharePoint BCS -> code calling OData- > CRM database
· Consume the RSS by "SharePoint RSS view web part" directly.
Summary: SharePoint RSS view web part -> OData- > CRM database
(not recommended as it could not be used in "SharePoint Search")
So OData is all things horrible because it is hard to eat :-(
Option 5: BizTalk
Biztalk is built for mapping systems together, unfortunately, this solution is only considered for large enterprises.
Summary: SharePoint BCS -> BizTalk Database - > CRM database
The BizTalk data centre can also provide data for any system.
Requires little code if users already have BizTalk
Deployment - Needs external work to deploy BizTalk server.
Option 6: OData 3rd Party solutions (doesn't exist)
Today SharePoint 2010 exposes lists and document libraries as OData, but does not natively consume OData.
What does this mean?
- CRM 2011 exposes it data as OData, but cannot consume OData
- SharePoint 2010 exposes it data as OData, but cannot consume OData
....and there are no 3rd party solutions to solve this...