SQL Server 2012 MDS and SharePoint 2013 Integration

  • Article

Recently I worked in a demo preparation with my Colleagues who are SQL/BI experts. They were preparing a demo on SQL Server MDS capability. Part of the presentation was to start a workflow within SharePoint if any changes happen in Master Data. They asked me to assist them in this part.

I started looking into “how to” part and found the first lesson:

https://msdn.microsoft.com/library/gg690195.aspx

I found that everything should start working provide you have a SharePoint site and a site workflow in that site in place. Then you just have to mention the Workflow Extender name which is SPWF – because it is SharePoint and then need to enter the site URL and the Workflow name.

Great, I created 2 site workflow one using SharePoint Designer and one with Visual Studio. We planned to change some value in Customers and tried to check whether it starts the workflow or not. Essentially we can pass values to the workflow I created using Visual Studio.

But, it did not work! Then I found an article which claims that we need to create a workflow extender for MDS Workflow Service exclusively
to do so:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/a31769eb-948c-4bb2-844d-72d70e34292b/master-data-services-2012-and-sharepoint-workflow

It seems it’s only for SQL Server 2012. Previous versions of SQL has that built in. Unfortunately our demo environment was with SQL Server 2012 SP1 and SharePoint 2013 RTM.

Well, I created the extender, compiled it and tried to implement the same with MDS Workflow Service. But, it started throwing error saying I am using assemblies which are of newer versions than the current environment. What I found was the culprits were “Microsoft.SharePoint.dll” and “Microsoft.SharePoint.WorkfloActions.dll”. This is because I am in SharePoint 2013 version and the Workflow Extender in example was in .Net version 3.5. Also it seems MDS wants it that way. Which was ideally using dlls from SP 2010.

Next thing I did I borrowed the dlls from a SP 2010 server compiled the Extender using them and now the MDS Workflow Service accepted the extender.

But, again in runtime we started receiving error because the SharePoint dlls are not located in the SharePoint Server (remember we are using SP 2010 dlls but it is a SP 2013 server).

So, I was in a catch 22 situation. We have to show a demo in next half a day time where we have a workflow with escalation scenario. Which includes any specific change in customer value will assign a task to a person and then if he does not attend it within 5 mins it will be assigned to the 2nd person. Also the task title should include the name of the customer where the data is changed.

Then I took a drastic step. I created a MDS Workflow Extender and within it I called the Lists.asmx web service of SharePoint and there I added a task to the Workflow Tasks list for the first person. Then I managed the rest of the show by a Site Workflow created using SharePoint Designer. This waits for few mins and then assign the task to the 2nd person. Below is the code sample I wrote for the Workflow Extender:

 

using Microsoft.MasterDataServices.WorkflowTypeExtender;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Xml;

 

namespace Services.MDSWorkflowExtender

{

     public class WorkflowTester : IWorkflowTypeExtender

    {

        #region IWorkflowTypeExtender Members

 

        public void StartWorkflow(string workflowType, System.Xml.XmlElement dataElement)

        {

            // Extract the attributes we want out of the element data.

            XmlNode NameNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/Name");

            XmlNode CodeNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/Code");

            XmlNode EnteringUserNode = dataElement.SelectSingleNode("//ExternalAction/MemberData/LastChgUserName");

 

            try

            {

                com.contoso.intranet.Lists listService = new com.contoso.intranet.Lists();

                listService.Credentials = System.Net.CredentialCache.DefaultCredentials;

                string strBatch = @"<Method ID='1' Cmd='New'><Field Name='Title'>Customertype is changed for following customer: " + NameNode.InnerText + "</Field><Field Name='AssignedTo'>18;#contoso\\pranab</Field></Method>";

                //Guid of the Workflow Tasks list

                string listGuid = "{cc49bc7b-ccde-4bfa-8458-f9bcf7a709d4}";

                //Guid for All tasks view

                string activeItemViewGUID = "{6780F7D4-A0B2-426A-AB86-2C4D8EEFC36A}";

                XmlDocument xmlDoc = new System.Xml.XmlDocument();

                System.Xml.XmlElement elBatch = xmlDoc.CreateElement("Batch");

                elBatch.SetAttribute("OnError", "Continue");

                elBatch.SetAttribute("ViewName", activeItemViewGUID);

                elBatch.InnerXml = strBatch;

                XmlNode ndReturn = listService.UpdateListItems(listGuid, elBatch);

            }

            catch (Exception)

            {

                throw;

            }

        }

 

        #endregion

    }

 

}

 

So the learning was if you are using SQL Server 2012, you should use custom Workflow Extender to integrate SharePoint Workflow and use SharePoint 2010 instead of SharePoint 2013 if you don’t want sleepless nights.