As a Sales Engineer at Scribe, I get many questions about Scribe capability in specific integration scenarios. One such question is how we can integrate Dynamics CRM and Microsoft SharePoint. In this post, I provide an overview of how I managed to integrate these two systems, some of the tools I had to use, and some of the pitfalls I ran into. I used the Scribe Adapter for Web Services and the Scribe Adapter for Dynamics CRM 2011 when designing this integration. I hope this helps shed some light on how this integration can work between these two systems.
Adapter for Web Services
This adapter is a generic connection to SOAP based web services provided by Scribe. It allows Insight to utilize many SOAP based web services that are publicly available, and also allows you to create your own in order to access legacy systems with poor or no database structure. You can utilize this to either pull or push data to the back end systems, as it exposes all the methods and parameters for the particular WSDL you are using. This adapter also exposes some additional configuration that can be used to streamline your integration design, like providing default values to parameters that can be overridden, logon and logoff methods, as well as error capturing using Regular Expressions.
SharePoint Web Services
Microsoft SharePoint has a very well documented web service back end to assist you in integrating with it. It is not just one web service, but instead a collection of roughly 21 separate WSDL endpoints that allow you to manage and manipulate data. When starting with these SharePoint web services I highly recommend utilizing MSDN for help on how to work with the web services as it is very well documented, gives you code samples that you can utilize to figure out what type of data needs to be passed in, and teaches you what each method does. SharePoint also has a unique way of working with separate sites and its web services. Instead of giving you just those 21 web service endpoints, if you have 3 separate sites built within SharePoint, you get 63 separate end points, 21 for each site. This makes it very easy to distinguish what the particular web service connection is being used for, removing all ambiguity from the integration design. In my case, I did two separate integrations, one for Help Desk tickets and one for Marking Campaigns, which had two separate sites in SharePoint. When I created my connections to the web services, I could see right inside the URL what site I was connecting to.
The integration that I will overview here is a Marketing Campaign integration, bringing Campaign data down to SharePoint lists. This includes not only the core data about the Campaign, but any related marketing material as attachments. The use case behind this scenario is that a company has users who need access to marketing material such as PowerPoint templates, email drafts, and white papers, but who do not have access to Dynamics CRM. This integration allows those users to utilize the corporate SharePoint server to gain access to materials that were previously only available inside Dynamics CRM.
When approaching this integration, several decisions had to be made up front about how this should run. I decided to make this a real time one way integration using Queue based integrations so that the data could be presented as soon as changes are made, and it would make use of multi-threading. In order to do this, I had to work outside of the box a bit. Because I need to bring all attachment data to SharePoint, I had to have a direct connection to my CRM system. This means that I could not just use the XML message as the source of my DTS file. So, to overcome this I used a Prompted Variable to capture the GUID of the campaign I was working with, and fed that into my source query using XPATH. That allows me to extract that CampaignID value from the XML that triggered the integration, and feed it into the source query at run time, giving me not only the campaign I care about, but also the attachments that come with it.
So, this setup will allow me to use a real time, multi-threaded integration, but I do not have to use XML as the source of the DTS file in order to make this happen.
Now, within the setup of the job itself there are some complex formulas that must be used in order to work with the SharePoint web services. There is the concept of an xmlAny attribute, which is how data must be sent to SharePoint. This attribute allows me to send in a piece of XML with field information and values to match what fields exist in SharePoint. It must be done this way because SharePoint is so easily customizable there is no way for the web service to constantly be re-configuring itself to display all the fields you are adding/changing within SharePoint. By using Scribe formulas to create XML header and node information, I am able to pass in whatever fields are available at that time, and if I need to make a change to add/remove a field, I simply alter the XML I am passing in. As you can see in the screen shot, I was able to create calculated variables which contained XML node information and data, and concatenate them together with XML header information to present a large block of XML with a minimal amount of data in my formula.
Another aspect in dealing with the SharePoint web service is working with return values. Much as is with passing data into SharePoint as XML, the values you receive from SharePoint are also formatted as XML. So, instead of creating formulas to stitch together XML, we must now find a way to parse the XML and locate the values we are looking for. I did this using text functions like FIND() and MID(), in order to locate the nodes I cared about, and get just that value from the XML. I could then use this in subsequent steps.
There are many more design details to this integration that can be found in our latest video within the Inside Track, if you would like to view this new video but are not yet a subscriber of the Inside Track, you can register for a free trial here using the registration code "InsideTrackTrial". For those of you who have already subscribed, there should be a new book on your shelf titled Integrating Dynamics CRM and SharePoint.