I am hoping someone can advise me on this.

I am working on synchronizing product information between Master Data Services and an ERP system using SSIS as the middleman. Products will primarily be added and modified in MDS, and this data will update the data in the ERP. I would, however, like to preserve the ability to update product records using the ERP interface when it is the most efficient means for the task. The updates made in the ERP system then need to update the corresponding records in MDS.  I was thinking of using Change Data Capture on the ERP tables to identify the record changes that need to be sent to MDS. What I am trying to figure out is, how do I keep updates originated from MDS and posted to the ERP tables from being flagged by Change Data Capture for update back to MDS?

Any advice would be appreciated.

 

In your SSIS for data going to MDS you can have a Lookup transform to test for the existence of the row. Or maybe CDC is not a proper tool for this project.

• Marked as answer by Mei152 Tuesday, December 03, 2013 5:41 AM
• Unmarked as answer by Mei152 6 hours 42 minutes ago

Thanks for the reply, Jim.

After thinking about it some more, I realize that there is much more involved in synchronization, such as managing concurrent record edits. I am probably better off restricting record maintenance to be done through MDS for the time being. 

I actually figured out how to do it.  The entities in my ERP system have a field  for each leaf member indicating the last user to update the record. It also has native integration tools that enable you to create import/export/update packages that can be invoked externally . All I need to do is set up a dedicated user code in the EPR for MDS imports. Any record edits made by this user code can be filtered out of the CDC results.

• Marked as answer by Mei152 6 hours 27 minutes ago