Creating the business rule is straightforward but problems start when it comes to importing data via staging tables.

Recently I had to set up an entity that had to have an unique code across the enterprise. In SQL Server 2012 version of MDS this is achieved quite easily just by checking the “Create Code values automatically” checkbox when creating the entity. In 2008 R2 version we have to leverage the business rules feature.

Action “defaults to a generated value” comes handy.

First, create a new business rule and choose the “defaults to a generated value” action and drag Code-attribute into the “Edit action”-section. Starting the incremental generation from number 1 is fine. Now we are ready. At least if we only use MDS web user interface to add new members to our entity. In my case I need to import the data from source system(s) and therefore I need to use staging tables.

Importing via staging tables new members that will have an auto-generated code

First try

One can not insert NULL values into the MemberCode column.

First of all, how the hell I’m supposed to insert any new members into the staging table since the MemberCode-column is not nullable? Luckily I found this MSDN Forum Thread where I was told to assign the legendary “” – empty string value to MemberCode. OK, works out well but the problem is that I also want to import some additional attributes (other than the mandatory Name and Code attributes) via tblStgMemberAttribute-table and there I need to have the unique MemberCode that is assigned after the model is validated or the business rules are executed. How am I supposed to assign the correct attributes to correct member if I don’t know the unique MemberCode because it’s generated after the model is validated? Now what?

How to import additional attribute values if we don’t know the Code value when importing?

Second try

We’re now facing bit of a “Chicken-and-egg problem” here. Luckily I came across another blog post that handled the very same issue. The trick is quite simple now when I think about it afterwards. The point is that when the new member records have been imported to MDS with empty string as their MemberCode value MDS will assign a special system-guid to the newly imported members:

Special “#SYS-“-prefixed GUID Codes are assigned to members that were imported with empty string as membercode.

These “#SYS-“-prefixed GUID values are only temporary because when the business rules are executed MDS replaces them with the value that we have set when defining the business rule. So how does this help us since the #SYS-values are still assigned AFTER the import and we need them BEFORE we import the rows via tblStgMemberAttribute-table? We simply “emulate” the MDS’s internal process by assigning the system guids by ourselves already in the loading phase by generating the unique system guids for example using the newid()-function in SQL Server.

Generating the #SYS GUID on our own by newid() function call.

Now, by assigning the system GUID ourselves to the tblStgMember and tblStgMemberAttribute tables we can successfully import new members with additional attributes and get the correct auto-generated code values by validating the model.