[Update - April 20, 2021] The techniques shown in this blog post may not be needed anymore if you are working with Dynamics 365 online v9.2 or later. The techniques are still applicable if you are on an earlier version of Dynamics 365 or Dynamics CRM installation. For more details, please check Nishant Rana's blog post or MSDN document.
Microsoft Dynamics 365/CRM entities often have some special statuses that would prohibit update of the entity record when there are any other fields involved but the record itself is in a certain status. One such entity is the CRM case entity which has a physical name of incident in the system. If a case is resolved, you can't make any further updates to the record, otherwise you will receive an error such as "This case has already been resolved. Close and reopen the case record to see the updates". The same can happen to other CRM entities with similar behaviors in which case you might receive a different message.
This imposes a challenges to your data integration (or ETL) process. Let's assume that your ETL process is designed to take changes from your source system, and make updates to the case records in the target system using an Update action. You would typically configure the write process using one single writing component (in SSIS, we call it the CRM destination component) which should be first updating all value fields (those fields except statecode and statuscode because they are special), then perform an update of the statecode and statuscode fields. Let's say that you are performing a data synchronization from one CRM system to another. Your data flow may look like something simple as below.
In the ETL destination component, you would typically use the Upsert action, so that it will try to find if a case record of the same id (incidentid) exists in the target system. If a match is found, the component will perform an update, otherwise it will create a new record in the target system. If the CRM entity does not have special statecode logic, the above data flow should work perfectly fine without any trouble. If there is a statecode logic like we just described above, when the component finds the incoming row is an existing record in the target system, but the matching incident record in the target system is already marked as Resolved or Canceled, and at the same time the incoming incident record has new values in other fields (those fields except statecode and statuscode), then the component will try perform an Update on those other fields first using the UpdateRequest message. This service call will sure fail with the error message for the reason that we just mentioned above. The component does not have the knowledge about the statecode situation, and it is unrealistic to expect an ETL component to handle this type of situation gracefully. With that said, this is the time that we, as the integration developer, bring a solution to the table.
With the above scenario in mind, the proper procedure is to first re-open the CRM entity record by updating its statecode to the default one which usually has the least limitations (or business logics associated). Then perform an Update of all other fields except statecode/statuscode. Lastly, we can update the statecode/statuscode fields to finalize the record's status. In summary, the process involves three steps.
- re-open the entity record
- update all other fields
- re-close if needed
Note that we only need to go through this 3-step procedure if we run into the error message related to statecode/statuscode restrictions. So before we kick off the procedure, we need to make sure the error message is about the statecode/statuscode restrictions. When using SSIS, we would be using a Conditional Split component to check the error message received from the first CRM destination component in the data flow. The following is how the data flow should look.
Note that the Conditional Split component should be attached to the first destination component's Error Output. It should use the FINDSTRING function to check the error message to make sure the error is relevant, the expression can be something shown below.
After the Conditional Split component, we need to create a temporary statecode value that we can use to re-open the case. We use a SSIS Derived Column component to achieve this by creating a hard-coded statecode value, let's call the new SSIS column ActiveStateCode. The Derived Column component will look something below.
Right after the Derived Column component, we will attach the second destination component that performs an Update action with only the primary key and statecode field mapped. The statecode field will receive the hard-coded value from the Derived Column component.
Lastly, in the same data flow task, we can finally perform the final Update of all fields including statecode and statuscode using a third CRM destination component. The third component will have almost identical mapping as the first one, except we use Update action this time (plus we can now map the modifiedby field which is not available in the first destination component, as it is using the Upsert action).
Once you have this data flow configured properly, you should then be able to run the data flow without worrying about encountering the same error which would fail to bring (or synchronize) the changes to the target CRM system.
This is a fairly common integration situation, I have created a sample SSIS package in SSIS 2008 R2 format (which should automatically upgrade if you use a newer version of SSIS) that you can download and play with it.
I hope you find this helpful.