In the article "Master Data Services: Find your way through the MDS database schema" we have read and updated attribute values by accessing the MDS database directly via T-SQL. Using the direct approach your code could break when a newer version of the MDS database schema is released, and that’s why it’s better to use the Master Data Services WCF API. In this article I am using the Master Data Services WCF API to update attribute values with auto-generated values, which is an enhancement to the code that has been used in the article "Master Data Services: Implementing a Custom Workflow " where T-SQL is used to directly update attribute values.
A business rule is triggered when a new item has been created; a XML message will be queued in the Service Broker and handled by the SQL Server MDS Workflow Integration service that calls the custom .NET assembly, which uses calls to the Master Data Services WCF API to update attribute values.
When you want to make use of the Master Data Services WCF API you have to enable the webservice for your MDS web application in the Master Data Services Configuration Manager.
After you created a website, behind the scenes several settings will be added to the web.config (can be found in the folder “C:\Program Files\Microsoft SQL Server\Master Data Services\WebApplication”). In the “masterDataServices” section the Master Data Manager configuration is configured, in the “connectionStrings” section the connection string to the MDS database, and in the “services” section the binding with the WCF web service. The configuration settings within the different sections are related to each other by labels (in this case “MDS1”).
To be able to use the Master Data Services WCF API you have to add a service reference to your customer .NET assembly project. If working locally, this service reference must reference the service http://localhost/MDS/service/service.svc. The following snippet of code shows how to update an attribute value by using the Master Data Services WCF API (the input variables are colored purple).
When the Master Data Services WCF API is used from a custom .NET assembly, it is running under the same credentials as the custom .NET assembly runs that inherit its credentials from the SQL Server MDS Workflow Integration service. When you use an account for the SQL Server MDS Workflow Integration service that has read-only permission for an attribute that you want to update like described in the previous section, you will get an error (Code = “300014”, Description = The attribute is read-only. You do not have permission to update the attribute.). You can solve this problem by using a service account for the SQL Server MDS Workflow Integration service that has update permissions for the models, entities, and attributes in MDS (has to be set in the Master Data Manager) or impersonate the custom .NET assembly to an account that does have update permissions.
Using the Master Data Services WCF API is the preferred way to update attribute values. You need a small learning curve to find your way through the API, but when you’ve done this, it works like a charm.
- Getting started with the web services API in SQL Server 2008 R2 Master Data Services
- Microsoft.MasterDataServices namespace