SharePoint Workflow Integration with Master Data Services

  • Article

SQL Server Technical Article

Writers: Tyler Graham, Suzanne Selhorn

Technical Reviewer: Minh Pham

Applies to: SQL Server 2008 R2 Master Data Services

Summary: This paper details the steps necessary to configure Microsoft® SQL Server® 2008 R2 Master Data Services to initiate a Microsoft® SharePoint® workflow.

Introduction

With Master Data Services, you can use business rules to create basic workflow solutions. You can automatically update and validate data and have e-mail notifications sent based on conditions you specify. Business rules in Master Data Services are intended to manage the most common workflow scenarios.

If your workflow needs require more complex event processing, such as multi-tiered approvals or complex decision trees, you can configure Master Data Services to send data to SharePoint and start a SharePoint workflow.

Each time you apply the start Workflow business rule to your entity, records that meet the business rule conditions are passed to the Service Broker queue. At regular intervals, the MDS Workflow Integration service calls a stored procedure in the Master Data Services database. This stored procedure pulls the records from the Service Broker queue and passes them to the SharePoint workflow.

Prerequisites

Before configuring Master Data Services to work with a SharePoint workflow, you must have the following software available:

  • SQL Server 2008 R2 Master Data Services November CTP or later
  • Microsoft® Visual Studio® 2010 Beta 2 Ultimate or later
  • Microsoft SharePoint Foundation 2010 or Microsoft SharePoint Server 2010

This software will be installed in the following procedures.

Workflow

To send master data from Master Data Services to a SharePoint workflow, you will complete the following steps.

  • Step 1: Install Master Data Services
  • Step 2: Install SharePoint
  • Step 3: Update the Web Configuration File
  • Step 4: Install and Start the Workflow Integration Service
  • Step 5: Install Visual Studio
  • Step 6: Create a Workflow in Visual Studio
  • Step 7: Create and Apply Business Rules in Master Data Manager

Step 1: Install Master Data Services

First, install Master Data Services. For more information, see the SQL Server 2008 Books Online topic “How to: Install Master Data Services” (https://msdn.microsoft.com/en-us/library/ee633762(SQL.105).aspx)https://msdn.microsoft.com/en-us/library/ee633762(sql.105).aspx).

This white paper uses C:\Program Files as the folder where Master Data Services is installed.

Step 2: Install SharePoint

Master Data Services requires a version of SharePoint to be on the server where Master Data Services is installed. This version of SharePoint can host the workflow, but it does not need to.

The minimum version of SharePoint required is SharePoint Foundation 2010. You can install SharePoint Foundation from:

https://technet.microsoft.com/en-us/sharepoint/ee263910.aspx

Note: The required collation for SharePoint Foundation database installations is Latin1_General_C1_AS_KS_WS. Other collations are not supported. For more information about setting up the SharePoint Server, see the SharePoint topic “Setting Up the Development Environment for SharePoint Server” (https://msdn.microsoft.com/en-us/library/ee554869(office.14).aspx).

Step 3: Update the Web Configuration File

You can now edit the Master Data Services Web configuration file to include the name of your Master Data Services server and database, and a SQL Server user that the service will use to access the stored procedure in the Master Data Services database.

Create a User with Access to the Stored Procedure

  1. Open SQL Server Management Studio and connect to the SQL Server Database Engine instance that hosts the Master Data Services database.
  2. Create a login named workflowusr. The password can be a secure password of your choice.
  3. For the Master Data Services database, create a user named mds_workflow_user and map it to the workflowusr login.
  4. After entering the name and login, click the Securables page and search for the stored procedures object type. Find [mdm].[udpExternalActionsGet] and grant EXECUTE permission to it.

Edit the Master Data Services Web Configuration File

  1. On the server where Master Data Services is installed, open Microsoft.MasterDataServices.Workflow.exe.config from C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.
  2. Find this setting: <setting name="ConnectionString" serializeAs="String">
  3. Update the value to reflect the server, database, and user. If your SQL Server installation uses case-sensitive collation, then the name of the database must be entered in the same case as in the database. For example, <value>Server=myServer;Database=myDatabase;Integrated Security=False;User Id=workflowusr; Password=myPassword</value>
  4. Save and close the file.

Step 4: Install and Start the Workflow Integration Service

Next, install and start the SQL Server MDS Workflow Integration service.

Copy the Install File

  1. On the server where Master Data Services is installed, open an elevated command prompt.
  2. Go to %Windows%\Microsoft.NET.
  3. If you have more than one Framework folder, determine which is the most recent and go to that folder.
  4. Go to the Framework folder’s subfolder, for example Framework\v4.0.21006.
  5. Confirm that InstallUtil.exe is in the folder.
  6. Copy InstallUtil.exe to the Master Data Services bin folder. At the command prompt, type: copy InstallUtil.exe C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin
  7. Press ENTER to copy the file. Do not close the command window.

Install the Service

  1. At the command prompt, go to C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.
  2. Type: InstallUtil Microsoft.MasterDataServices.Workflow.exe
  3. Press ENTER. A dialog is displayed, prompting for a user name and password.
  4. Type a Windows domain user name and password. It is recommended that the user be the W3WP Identity on the SharePoint Application Pool. Alternately, the user must have Read-Write access to the SharePoint Content Database.
  5. Click OK.

Start the Service

  1. From the Start menu, click Run.
  2. Type: Services.msc
  3. Click OK. The Services snap-in opens.
  4. Find the service called SQL Server MDS Workflow Integration.
  5. Double-click the service.
  6. Click Start.
  7. Click OK.

Step 5: Install Visual Studio

Install Visual Studio 2010 Beta 2 or later. You will use Visual Studio to create your workflow.

You can install Visual Studio from:

https://www.microsoft.com/visualstudio/en-us/products/2010/default.mspx

Step 6: Create a Workflow in Visual Studio

Create the SharePoint Workflow

Now you can create the SharePoint workflow for your organization.

  1. Open Visual Studio 2010.
  2. Using the SharePoint 2010 templates, create a ‘Sequential workflow’ project.
  3. Drag the OnWorkflowActivated control from toolbox to the designer for your solution.
  4. Right-click the control and choose Generate Handlers.
  5. The data passed from the workflow service is contained in this string: string MDSData = workflowProperties.InitiationData;
  6. Compile and deploy your workflow.

Add a DLL Reference to Workflowactions.dll

For your workflow to work, you must add a DLL reference to a file on the SharePoint server that will host the workflow.

Note: The SharePoint server that will host the workflow can be the server where you installed SharePoint in step 2, or it can be another SharePoint installation.

  1. In Visual Studio, in Solution Explorer, right click References.
  2. From the menu that is displayed, click Add Reference.
  3. On the SharePoint server that will host the workflow, browse to %Program files%\Common Files\Microsoft Shared\Web Server Extensions\14\ISAPI\Microsoft.Office.Workflow.Actions.dll.
  4. Click OK.

Step 7: Create and Apply Business Rules in Master Data Manager

Next, create a business rule in Master Data Manager. When data is validated against the business rule, the Service Broker queue is populated. The service checks the queue, sends the data to SharePoint, and then clears the queue.

Create and Publish a Business Rule

You must first create and publish the business rule that will start the workflow when applied.

You should ensure that your business rule contains actions that change attribute values, so that the rule evaluates to false after it has been applied once. For example, your business rule might evaluate to true when a Price attribute value is greater than 500 and the Approved attribute value is blank. The rule can then include two actions: one to set the Approved attribute value to Pending and one to start the workflow.

Alternatively, you may want to create a rule that uses the “has changed” condition and add your attributes to change tracking groups. Note: This functionality is not available in SQL Server 2008 R2 November Community Technology Preview and earlier.

To create a business rule in Master Data Manager, you must have permission to the System Administration functional area and be a model administrator for the model you want to create the business rule for.

  1. In Master Data Manager, click System Administration.
  2. From the menu bar, point to Manage and click Business Rules.
  3. On the Business Rule Maintenance page, select a model, entity, member type, and attribute.
  4. Click Add business rule. A row is added to the table and is highlighted.
  5. Click Edit selected business rule.
  6. In the Components pane, expand the Conditions node.
  7. Drag conditions to the IF pane’s Conditions node.
  8. In the Entity-Specific Attributes pane, click an attribute and drag it to the Edit Action pane’s Select attribute label.
  9. In the Edit Condition pane, complete any fields and click Save item.
  10. In the Components pane, expand the Actions node.
  11. Under External action, drag Start Workflow to the THEN pane’s Action label.
  12. In the Entity-Specific Attributes pane, click any attribute and drag it to the Edit Action pane’s Select attribute label. This attribute has no bearing on the workflow process.
  13. In the Edit Action pane, in the Workflow type box, type SPWF.
  14. Optionally, select the Include member data check box. Choose this to include attribute names and values in the information passed to SharePoint.
  15. In the Workflow site box, type the name of your SharePoint site. For example, http://site_name.
  16. In the Workflow name box, type the name of your workflow from Visual Studio. In Visual Studio Solution Explorer, this value is in the Display Name field.
  17. At the bottom of the page, click Save.
  18. At the top of the page, click Back. The Business Rule Maintenance page opens.
  19. Click Publish business rules.
  20. On the confirmation dialog box, click OK. The rule's status changes to Active.

Apply Business Rules

Now you must apply the business rule to your data. This action passes the data to the Service Broker queue.

  1. From the Master Data Manager home page, click Explorer.
  2. Select the entity with members you want to validate.
  3. Click Apply business rules.

The SQL Server Service Broker queue is populated. When the service checks the queue, it sends the data to SharePoint and clears the queue.

Troubleshooting

When configuring Master Data Services to work with a SharePoint workflow, you might encounter any of the following issues.

SharePoint Doesn’t Receive Data

If SharePoint doesn’t receive the data, you can try debugging the workflow service or viewing the Service Broker queue to determine if the correct data is still in the queue.

Debug the Workflow Service

  1. Stop the service if it is running.
  2. Open a command prompt.
  3. Go to the location of your service and type: Microsoft.MasterDataServices.Workflow.exe –console
  4. Press ENTER.
  5. In Master Data Manager, update your attribute and apply business rules again. Detailed logs are displayed in the console window.

View the Service Broker Queue

The Service Broker queue that contains the master data passed as part of the workflow is: mdm.microsoft/mdm/queue/externalaction

If the service cleared the queue properly, this queue should be empty.

If there is data in the queue that you want to remove, you can run the following SQL script to clear the queue. You must change myDatabase to the name of your Master Data Services database.

declare @conversation uniqueidentifier

while exists (select 1 from [myDatabase].[mdm].[microsoft/mdm/queue/externalaction])

begin

set @conversation = (select top 1 conversation_handle from [myDatabase].[mdm].[microsoft/mdm/queue/externalaction] )

end conversation @conversation with cleanup

end

SharePoint Workflow Isn’t Starting

For more information on debugging a SharePoint workflow, see “Walkthrough: Creating and Debugging a SharePoint Workflow Solution” (https://msdn.microsoft.com/en-us/library/bb386168.aspx).

For more information:

https://www.microsoft.com/sqlserver/: SQL Server Web site

https://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

https://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release. Send feedback.