How to fix "MDSModelDeploy -update" attribute error preventing automated deployment?

Prochazka, Vladimir 35 Reputation points
2023-04-20T14:40:06.8933333+00:00

SQL Server 2019, build 15.0.4138.2
MDSModelDeploy.exe, version 15.0.2000.5 DEV, MasterDataServices
PROD, MasterDataServices We are performing automated MDS Model deployment between DEV and PROD using powershell scripts and MDSModelDeploy. Following scenario is causing issue, preventing automated deployment. DEV - Create model, entity and at least one attribute as Text, Length : 100User's image

DEV - Create PKG file using "MDSModelDeploy createpackage" command

cd "C:\Program Files\Microsoft SQL Server\150\Master Data Services\Configuration\"    .\MDSModelDeploy.exe createpackage -service "MDS1" -model "X - Test model" -package "X - Test model.pkg" 

PROD - Copy PKG file and create clone of the model using "MDSModelDeploy deployclone" command

   .\MDSModelDeploy.exe deployclone -service "MDS1" -package "X - Test model.pkg" 

Models on DEV and PROD now match including Model, Entity and Attribute MUIDs in mdm.tblModel, mdm.tblEntity and mdm.tblAttribute tables

   SELECT * FROM mdm.tblModel WHERE Name = 'X - Test model'
   SELECT * FROM mdm.tblEntity WHERE Name = 'TstEntity'
   SELECT * FROM mdm.tblAttribute WHERE Name = 'Attr1'

DEV - change attribute length to 50; please note that changing attribute length will cause DEV attribute MUID to change (basically what is happening in the background is a new attribute is being created, existing data converted to new type and copied to a new attribute, original attribute deleted and new attribute renamed to original attribute name)User's image

DEV - Create PKG again file using "MDSModelDeploy createpackage" command

   .\MDSModelDeploy.exe createpackage -service "MDS1" -model "X - Test model" -package "X - Test model.pkg"

(Problem1) PROD - Model update using "MDSModelDeploy deployupdate" command fails, because there is already attribute with the same name, but different MUID. Cause is obviously MUID change in step 5. (Workaround1) While not optimal, this error still can be bypassed/automated by updating PROD attribute MUID to DEV MUID using SQL script.

   .\MDSModelDeploy.exe deployupdate -service "MDS1" -package "X - Test model.pkg"
   MDSModelDeploy operation failed. Elapsed time: 00:00:01.5185421
   Error:
   The model cannot be updated.
   110003: The name already exists. Type a different name.
   Correlation Id: 9cde359e-ad48-432d-ac6e-e29ab0861266
   Package deployment failed with 1 errors
   The name already exists. Type a different name.
   UPDATE mdm.tblModel
   SET MUID = '<DEV MUID>'
   WHERE Name = 'X - Test model'

(Problem2) PROD - Model update using "MDSModelDeploy deployupdate" command after MUID update will pass without error, however PROD attribute type will remain Text, Length : 100. Only way to change attribute length is manually with subsequent PROD MUID update (to match DEV MIUD again) or possibly (haven't tried) by running same stored procedures as are run from UI. However, both options make usage MDSModelDeploy.exe pointless and automatic deployment via PKG non-functional. User's image

From my point of view this is a MDSModelDeploy.exe bug.
However, if you are aware of a working (preferably fully automated) solution let me know.
Please, don't bother with suggestions like incorrect PKG file copy/overwrite, focus on MDSModelDeploy.exe Thank you

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,872 questions
0 comments No comments
{count} votes