Resources For IT Professionals

United Kingdom (Proper English-like)

Skip to locale bar

•  

Configuring Workflow Integration with Master Data Services - TechNet Articles - United States (English) - TechNet Wiki

Grant stored procedure permissions to the MDS Workflow service user

For the greatest security, it is recommende/span>

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 new login for the mds_workflow_service user you created in the previous section. To create a new login, open the Security node in the Object Explorer, right click the Logins node, and select New Login…. In the Login dialog, enter <your server name>\mds_workflow_service for the Login name, make sure Windows authentication is selected, change the default database to the Master Data Services database, and click OK.

3. Create a user for the Master Data Services database and map it to the mds_workflow_service login. To create and map a user, right click the mds_workflow_service login in the Object Explorer, and select Properties. On the properties dialog, navigate to the User Mapping page, check the Map checkbox for the Master Data Services database, and click OK.

4. Grant the mds_workflow_service user permission to the stored procedure required for the MDS Workflow Integration service. To do this, open the Master Data Services database node in the Object Explorer, open the Security and Users nodes, right click the mds_workflow_service user, and select Properties. On the properties dialog, navigate to the Securables page, click the Search button and search for all objects of the stored procedures object type. Find [mdm].[udpExternalActionsGet] in the list and grant EXECUTE permission to it.

Step 5: Create a Workflow Handler in Visual Studio

A workflow handler is a .NET class library you create that can perform any actions you specify. One common scenario is to define SharePoint integration. In this case, the MDS workflow triggers the workflow created in the SharePoint site.

Create a SharePoint workflow

If you want to integrate MDS with SharePoint, you should start with the creation of the SharePoint workflow for your organization. If the MDS extender you are going to create does not rely on SharePoint, you don’t need to perform the steps below.

1. In Visual Studio 2010, create a new ‘Sequential Workflow’ project, select ‘site Workflow’ and complete the wizard . This project type is found in the SharePoint 2010 templates.

2. An OnWorkflowActivated control is included by default in the designer for your solution. Right-click the control and choose Generate Handlers.

3. Visual Studio generates a class that inherits from the SequentialWorkflowActivity class. The class contains a workflowId property that contains the ID of the workflow, a workflowProperties class that contains data associated with the activity, and a method named onWorkflowActivated1_Invoked that is called when the workflow is activated. If you want to perform any custom handling for the workflow, include it in this method. The data that is passed from the workflow service is stored as a string in workflowProperties.InitiationData.

4. Compile and deploy your workflow. To do this, right-click the project in Solution Explorer and click Deploy.

5. Verification Step: Once the SharePoint workflow is deployed, start it manually and verify that all the implemented actions are working as expected. If this step succeeds, we’ll be able to assume that the SharePoint workflow was successfully created and deployed.

Create a workflow extender

A workflow extender is a .NET class library assembly that implements the Microsoft.MasterDataServices.WorkflowTypeExtender.IWorkflowTypeExtender interface. The workflow extender assembly must be a .NET 20 assembly, not a version after .NET 2.0. The MDS Workflow Integration service calls the StartWorkflow method to run your code. Follow these steps to create an assembly and configure the MDS Workflow Integration service to call its interface.

Sample code for a SharePoint workflow extender and for a test workflow is associated withthe SharePoint workflow was successfully created and deployed.

Create a workflow extender

A workflow extender is a .NET class library assembly that implements the Microsoft.MasterDataServices.WorkflowTypeExtender.IWorkflowTypeExtender interface. The workflow extender assembly must be a .NET 20 assembly, not a version after .NET 2.0. The MDS Workflow Integration service calls the StartWorkflow method to run your code. Follow these steps to create an assembly and configure the MDS Workflow Integration service to call its interface.

Samp this document. You can use these files to guide you in the creation of your own extender or you can import the class into your extender project and leverage the existent code. The extender class should replace the Class1.cs file in the instructions below (import the file instead of following steps 3-7).

1. In Visual Studio, create a new Class Library project that uses the language of your choice. To create a C# Class Library, select the Visual C#\Windows project types and select the Class Library template. Enter a name for your project, such as MDSWorkflowExtender, and click OK.

2. Add a reference to Microsoft.MasterDataServices.WorkflowTypeExtender.dll. In the Solution Explorer pane, right click References and select Add Reference…. In the Add Reference dialog, go to the Browse tab and navigate to C:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\bin. Select Microsoft.MasterDataServices.WorkflowTypeExtender.dll and click OK.

3. Open the Class1.cs file by double-clicking it in the Solution Explorer pane.

4. Add ‘using Microsoft.MasterDataServices.WorkflowTypeExtender;’ below the other using statements.

5. Rename your class to MDSWorkflowExtender, and inherit from IWorkflowTypeExtender. The class declaration should look something like the following:
   
   public class MDSWorkflowExtender : IWorkflowTypeExtender

6. Implement the interface. Right-click IWorkflowTypeExtender in the code file, select Implement Interface\Implement Interface in the menu. This creates stub code for all members in the interface.

7. Add whatever code you desire to the StartWorkflow method. This method is called by the MDS Workflow Integration service to start your workflow. Complete sample code and a description of the parameters of the StartWorkflow method are included in the last section of this document.

8. Build the solution.

9. Copy your MDSWorkflowExtender.dll assembly to the location of the MDS Workflow Integration service executable, in C:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\bin.

10. Depending on the configuration of your sever, you may need to grant the mds_workflow_service user READ and EXECUTE permissions on MDSWorkflowExtender.dll. In Windows Explorer, right-click MDSWorkflowExtender.dll and select Properties. In the Properties dialog, go to the Security tab, click Edit…, click Add…, and add the <server name>\mds_workflow_service user. The mds_workflow_service user is granted READ and EXECUTE permissions by default. Click OK to close all the dialogs that have opened.

11. Grant the mds_workflow_service user any additional permissions it needs to perform the workflow operations, such as READ and WRITE permissions on another database in your system.

Step 6: Update the MDS Workflow Integration service Web Configuration File

Edit the Master Data Services web configuration file

If you are using Windows authentication you do not have to specify a user or password in the configuration file. Alternately, if you use SQL Server authentication, specify the User ID and Password of a SQL Server login that has the necessary permissions on the Master Data Services database.

1. On the server where Master Data Services is installed, open an elevated command prompt.

2. Open Microsoft.MasterDataServices.Workflow.exe.config in Notepad from C:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\bin.

3. Find this setting:
   <setting name="ConnectionString" serializeAs="String">

4. Update the value to reflect the server and database. 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=True</value>.

5. Below the ConnectionString setting add another <setting> tag for your custom workflow. For example:
   
   <setting name="WorkflowTypeExtenders" serializeAs="String"> <value>SPWF=Microsoft.MasterDataServices.SharePointWorkflow.SharePointWorkflowExtender, Microsoft.MasterDataServices.SharePointWorkflow</value>
   </setting>

6. The following guidelines will help you construct your <setting> tag:

   1. Below the ConnectionString setting add another <setting> tag for your custom workflow. For example:
      
      <setting name="WorkflowTypeExtenders" serializeAs="String"> <value>SPWF=Microsoft.MasterDataServices.SharePointWorkflow.SharePointWorkflowExtender, Microsoft.MasterDataServices.SharePointWorkflow</value>
      </setting>

   2. The inner text of the <value> tag is in the form of <Workflow ID>=<assembly-qualified workflow type name>.

   3. <Workflow ID> is a string you use to identify this workflow assembly when you create a business rule in Master Data Manager.

   4. <assembly-qualified workflow type name> is the namespace-qualified name of your workflow class, followed by a comma, followed by the display name of the assembly.

   5. If your assembly is strongly named, you also have to include version information and its PublicKeyToken.

   6. If you have created multiple workflow handlers for different kinds of workflows, they should be “;” separated in the <value> tag.

7. Save and close the file.

8. Depending on the configuration of your server, you may see an “Access is denied” error when you try to save the file. If this occurs, temporarily disable User Account Control (UAC) on the server. To do this, open Control Panel, click System and Security. Under Action Center, click Change User Account Control Settings. In the User Account Control Settings dialog, slide the bar to the bottom so that you are never notified. Click OK. Click Yes in the confirmation dialog. Restart your computer and repeat the above procedure to edit your configuration file. After saving the file, reset your UAC settings to the default level.

9. To do verification, proceed as follows:

   1. Open a command prompt.

   2. Go to the location of your service and run the service in console mode by typing: Microsoft.MasterDataServices.Workflow.exe -console.

   3. Press ENTER.

   4. Expect output similar to the one below. The example below uses the SharePointWorkflowExtender defined in the sample file. If you have custom code, the assembly name and workflow identifier will be different, but the key point is to ensure that your workflow extender was loaded properly.

Loading workflow type extenders. For test purposes you may use: Microsoft.MasterDataServices.Workflow.WorkflowTypeTest, Microsoft.MasterDataServices.Workflow, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
Loading workflow type extender: SPWF=Microsoft.MasterDataServices.SharePointWorkflow.SharePointWorkflowExtender, Microsoft.MasterDataServices.SharePointWorkflow, Version=1.0.0.0
Loaded workflow type extender for: SPWF
Testing database connection.
Database connection successfully tested.

Step 7: Install and Start the Workflow Integration Service 

 Install and start the SQL Server MDS Workflow Integration service.

Grant read and execute permission to the MDS workflow user 

Depending on the configuration of your server, you may need to grant the mds_workflow_service user READ and EXECUTE permissions on the MDS Workflow Integration service executable.

1. On the server where Master Data Services is installed, open Windows Explorer and go to C:\Program Files\Microsoft SQL Server\110\Master Data Services\WebApplication\bin.

2. Right-click Microsoft.MasterDataServices.Workflow.exe and select Properties.

3. In the Properties dialog, go to the Security tab, click Edit…, click Add…, and add the <server name>\mds_workflow_service user. The mds_workflow_service user is granted READ and EXECUTE permissions by default.

4. Click OK to close all the dialogs that have opened.

Find the install utility

1. On the server where Master Data Services is installed, open an elevated command prompt.

2. Go to %windir%\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.30319.

5. Confirm that InstallUtil.exe is in the folder.

6. Copy the path for InstallUtil.exe, for example: C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil.exe.

Install the service 

1. At the command prompt, go to C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication\bin.

2. Type: C:\Windows\Microsoft.NET\Framework\v4.0.30319\InstallUtil Microsoft.MasterDataServices.Workflow.exe

3. Press ENTER. A dialog is displayed, prompting for a user name and password.

4. Type <server name>\mds_workflow_service for user name and the password you specified when you created the user. The user must have Read and Execute permissions to any assemblies that handle the workflow processing, as well as appropriate access to any resources required by the workflow handler assembly, such as Read-Write access to a database that the workflow handler will modify.

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 and select it.

5. Click the Start link.

6. After the service starts, close the Services snap-in.

7. Verification step: Wait for a few seconds, refresh the Services snap-in and ensure that the SQL Server MDS Workflow Integration service is running.

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

Create a business rule in Master Data Manager. When data is validated against the business rule, the Service Broker queue in the Master Data Services database is populated. The service periodically checks the queue, sends the data to the specified workflow handler, 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.

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. For more information, see  Administrators (Master Data Services).

1. In Master Data Manager, click System Administration.

2. F="line-height:115%;font-family:'segoe ui',sans-serif;font-size:12pt;">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. For more information, see  Administrators (Master Data Services).

   rom 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 the tag that identifies your workflow handler. To start the sample SharePoint workflow, for example, type SPWF.

14. Optionally, select the Include member data check box. Choose this to include attribute names and values in the XML that is passed to the workflow handler.

15. In the Workflow site box, type the name of a website. For a SharePoint workflow, this must be your SharePoint site. For example, http://site_name. For a custom workflow this may not apply, but can be used for added context.

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. For a custom workflow this may not apply, but can be used for added context. Be sure to type the workflow name exactly as it appears in Visual Studio.

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. Edit the entity with members you want to validate.

3. Apply Click Apply business rules.

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

Troubleshooting

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

The Workflow Handler Doesn’t Receive Data

If the workflow handler doesn’t receive 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 run the service in console mode by typing: 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. This queue can be found in SQL Server Management Studio by opening the Master Data Services database in Object Explorer, then opening Service Broker and Queues. Right-click the queue and select Select Top 1000 Rows to view the top 1000 rows in the queue.

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

Custom Workflow Example Code and XML Description

When you create a custom workflow class library, you create a class that implements the Microsoft.MasterDataServices.Core.WorkflowTypeExtender.IWorkflowTypeExtender interface. This interface includes one method, StartWorkflow, that is called by the MDS Workflow Integration service when a workflow starts. The StartWorkflow method contains two parameters: workflowType contains the Workflow type you entered in Master Data Manager, and dataElement contains metadata and item data for the item that triggered the workflow business rule.

Code Example

The TestWorkflow sample code associated with this document shows you how to implement the StartWorkflow method to extract the Name, Code, and LastChgUserName attributes from the XML data for the element that triggered the workflow business rule, and how to call a stored procedure to insert them into another database.

The StartWorkflow method receives a block of XML that contains metadata and data about the item that triggered the workflow business rule. The following example shows what the XML looks like:

<ExternalAction> 
  <Type>TEST</Type>
  <SendData>1</SendData>
  <Server_URL>This is my test!</Server_URL>
  <Action_ID>Test Workflow</Action_ID>
  <Model_ID>5</Model_ID>
  <Model_Name>Customer</Model_Name>
  <Entity_ID>34</Entity_ID>
  <Entity_Name>Customer</Entity_Name>
  <Version_ID>8</Version_ID>
  <MemberType_ID>1</MemberType_ID>
  <Member_ID>12</Member_ID>
  <MemberData> 
    <ID>12</ID>
    <Version_ID>8</Version_ID>
    <ValidationStatus_ID>3</ValidationStatus_ID>
    <ChangeTrackingMask>0</ChangeTrackingMask>
    <EnterDTM>2011-02-25T20:16:36.650</EnterDTM>
    <EnterUserID>2</EnterUserID>
    <EnterUserName>MyUserName</EnterUserName>
    <EnterUserMuid>EEF91D48-B673-4D83-B95F-5A363C11DE91</EnterUserMuid>
    <EnterVersionId>8</EnterVersionId>
    <EnterVersionName>VERSION_1</EnterVersionName>
    <EnterVersionMuid>52B788C2-2750-4651-9DB0-2CB05A88AA5A</EnterVersionMuid>
    <LastChgDTM>2011-02-25T20:16:36.650</LastChgDTM>
    <LastChgUserID>2</LastChgUserID>
    <LastChgUserName>MyUserName</LastChgUserName>
    <LastChgUserMuid>EEF91D48-B673-4D83-B95F-5A363C11DE91</LastChgUserMuid>
    <LastChgVersionId>8</LastChgVersionId>
    <LastChgVersionName>VERSION_1</LastChgVersionName>
    <LastChgVersionMuid>52B788C2-2750-4651-9DB0-2CB05A88AA5A</LastChgVersionMuid>
    <Name>Test Customer</Name>
    <Code>TC</Code>
  </MemberData>
</ExternalAction>

The following table describes some of the tags contained in this XML:

For more information:

http://go.microsoft.com/fwlink/?LinkId=243059: Master Data Services documentation

http://msdn.microsoft.com/en-us/sqlserver/ff943581.aspx: Master Data Services developer center

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

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

http://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.