CODEWITHSUNDEEP
sql-serversql-server-2012master-data-servicesmaster-data-managementmds
Durga
Durga

Reputation: 33

Master Data Services

I'm a SQL Server developer learning MDS. I loaded some entities via staging tables and via Excel add-in.

I'm trying to update members in an entity in MDS via the staging table. I can successfully add new members, but any attribute updates to existing members aren't populated to the entity view. The import process runs successfully with no errors.

I've tried ImportType = 0 and 2, neither works. When I set to 1, as expected I get an error. I also tried to update the code value using the NewCode column and that also does not get updated.

I've set up staging data with an SSIS package, and also with direct T-SQL INSERT INTO statement.

I am using almost the same T-SQL INSERT statement for a test entity which I created to load a new member, and then to modify attributes for the new member in a second batch.

Do you have any ideas why the updates would be ignored, or suggestions for things I can try?

Upvotes: 0

Views: 3034

Answers (5)

Ramchandra
Ramchandra

Reputation: 1

Using Import type =0 shall help u update the new attributes untill the updated new attribute has NoT Null Data. If it is so then Update shall fail. Recheck the data in entity.

If that doesn't work. Please try to refresh the cache in Model and try to get the entiry details again.

Learn more about import types in MDS from below link: https://learn.microsoft.com/en-us/sql/master-data-services/leaf-member-staging-table-master-data-services?view=sql-server-2017

Hope this helps.

Upvotes: 0

Nathan Li
Nathan Li

Reputation: 1

While you can update importstatus_id in the stg.leaf table. update stg.C_Leaf set ImportStatus_ID = 0 While I think it will force the data to be ready for staging and load to mdm entity.

Upvotes: 0

Prashant Bhosale
Prashant Bhosale

Reputation: 1

You should insert the data into staging table with ImportType as 0 or 2 along with the batchtag and then run the staging stored procedure to load the data from staging table to entity table. SP will compare the data from staging table with the data in entity table based on Code value and update the data in entity table.

Upvotes: 0

Harsha
Harsha

Reputation: 1

As suggested above Member error details view describe the error Make sure that you are checking below points when updating in MDS 1) Put code column in your INSERT statement 2) Include all columns of staging table in INSERT query when using importType = 2 (Otherwise all column will be updated as NULL)

Upvotes: 0

UselessSQL
UselessSQL

Reputation: 46

Look at your batch in the staging table to see if an errors occurred. If the "ImportStatus_ID" = 2 then the record failed to import. You can see the reason for failure by querying the view that shows reasons for the import failures. The view will be named "stg.viw_EntityName_MemberErrorDetails.

Here is a Microsoft link for reference: https://technet.microsoft.com/en-us/library/ff486990(v=sql.110).aspx

Hope this helps.

Upvotes: 0

Related Questions