Initiating MDS staging process using Stored Procedures

Question

Hi,

I am bulk load data from Legacy to MDS using staging tables. After load data in tblStgBatch, tblStgMembers and tblStgMemberAttributes. I am trigger the staging process using the following stored procedure calls.

              --Create staging batch
		EXEC MDS.mdm.udpStagingBatchSave @UserID = @User_ID, @VersionID = @Version_ID, @Name = @BatchName, @StatusID = 1, @ReturnID = @Batch_ID OUTPUT 
		
		
		INSERT INTO MDS.mdm.tblStgMemberAttribute (Batch_ID, UserName, ModelName, EntityName, MemberType_ID, MemberCode, AttributeName, AttributeValue) 
		SELECT @Batch_ID, @pUserName, @pModelName, EntityName, 1, MemberCode, AttributeName, AttributeValue
		  FROM @tblAttribute
		-- WHERE MemberCode IN (SELECT @pCountryName+'-'+Code FROM @tblMember)
		 
		SET @AttRowCount += ROWCOUNT_BIG()

		--Update staging batch
		EXEC MDS.mdm.udpStagingBatchSave @UserID = @User_ID, @VersionID = @Version_ID, @BatchID = @Batch_ID, @StatusID = 1, @TotalMemberCount = @MbrRowCount, @TotalMemberAttributeCount = @AttRowCount, @TotalMemberRelationshipCount = 0

		--Invoke staging
		EXEC MDS.mdm.udpStagingProcess @User_ID = @User_ID, @Version_ID = @Version_ID, @StagingType_ID = 4, @LogFlag = 1, @DoValidate = 0, @Batch_ID = @Batch_ID

Even after a successful load to MDS the status_id = 1 and not set to "2" in tblStgBatch table?

Only thing I am doing different is that I have two separate Batches to load Members and Attributes i.e. I have to records in tblStgBatch one for members and other for Attributes.

Does the status in tblStgBatch is dependent on tblStgMembers and tblStgMemberAttributes status or they are independent?

Thanks

Rehan

Answer 1

Error may occur during staging.It updates each row in staging table as status_ID coloumn
1- Staging is successful
2-Failure
In case its 2 chech for error code and it can help you to troubleshoot.

If you are getting 1 than there is no issue.Check if you see any information/error messages  in Web App of MDS

Answer 2

Thanks Anup,

Yes in tblStgMember and tblStgMemberAttribute Status_id =1 is successful and 2 is failure. But in tblStgBatch Status_id = 1 (Queued to run) where as Status_id = 2 (Not running i.e. stopped after running)

http://msdn.microsoft.com/en-us/library/ff486976.aspx

My issue is that my Status_id in tblStgBatch never changes to Status_id =2 even if there are no failures in tblStgMember and tblStgMemberAttribute.

Answer 3

Oops Missed!My Bad!

So your batch is not changing from queued to running to Not Running state.

1.Please check if you servicebroker is enabled/running or you can see any error in eventviewer from service broker.

2.you can manually trigger it by executing the procedure udpStagingBatchQueueActivate

Answer 4

 Run the following code this will kick of the staging process instantly

SELECT @versionid = mv.ID FROM mdm.tblModelVersion mv
INNER JOIN mdm.tblModel m ON mv.Model_ID = m.ID
WHERE m.Name = @ModelName AND mv.Name = @ModelVersionName
EXEC mdm.udpStagingSweep @UserID, @versionid, 1