Search code examples
sql-serverssisdynamics-crmssmskingswaysoft

Why do I get the Error Message: "No modifications to the 'SYSTEM' or 'INTEGRATION' user are permitted.", when I add an Optional default Value?

I'm migrating some Database from SSMS to CRM Online. And the problem is, that there are systemuser in SSMS Database, which were deleted in CRM Online, or worse the systemuser has a new GUID. In this case I tried to look for similarities. I found out, that the systemusers from both System have the same value in the field "internalemailaddress". So I used it in my Dynamics CRM Destination. And added an value for the "optional default value".

InternalEmailAddress

And this Error code came out: CRM service call returned an error: No modifications to the 'SYSTEM' or 'INTEGRATION' user are permitted.

On the other site, I could use the fullname, which I did with an "optional default value".

Fullname

And this gave me this Error code: CRM service call returned an error: systemuser With Id = 11111-111-AAAA-BBBB-123456789 Does Not Exist

and

CRM service call returned an error: An unexpected error occurred.

And when I don't write anything in the "Optional default value" I get this Error message: KingswaySoft.IntegrationToolkit.DynamicsCrm.LookupFailureException: Text lookup failed for 'managerid' field, entity 'systemuser' does not contain a record that has a value of '' for 'fullname' field (SSIS Integration Toolkit for Microsoft Dynamics 365, v11.1.0.7311 - DtsDebugHost, v15.0.2000.93)

Does someone know the problem and the solution? I would be really grateful.

Solution

  • My first mistake was, that I tried to give the GUID in the "internalEmailAddress". To solve it, I did an LEFT JOIN where I get all Territory's and their SystemUsers. My SQL Query:

    SELECT Territory.*, ModifiedBy.InternalEMailAddress as M_EMail,
CreatedBy.InternalEMailAddress as C_EMail, ManagerId.InternalEMailAddress as Manager_Email
FROM TerritoryBase as Territory
LEFT JOIN SystemUser as ModifiedBy
ON Territory.ManagerId = ModifiedBy.SystemUserId
LEFT JOIN SystemUser as CreatedBy
ON Territory.CreatedBy = CreatedBy.SystemUserId
LEFT JOIN SystemUser as ManagerId
ON Territory.ManagerId = ManagerId.SystemUserId

    Then in the Destination I assigned the columns like:

    Source             Destination
C_Email            CreatedBy
M_Email            ModifiedBy
Manager_Email      ManagerId

    And the Text Lookup Editor is like: InternalEmailAddress(E-Mail from SystemUser)

    This solved the problem, but I still got this error: CRM service call returned an error: No modifications to the 'SYSTEM' or 'INTEGRATION' user are permitted.

    The problem for this Errorcode was, that I had multiple SystemUser in Territory in the field "ManagerId", that didn't exist in the Online CRM. And since I put a "default option value", all the missing SystemUsers were assigned to 1 SystemUser. And in CRM you cannot assign 1 SystemUser to more Territory's ManagerId's field. So the end result was to not fill the field, if the user didn't exist.

    But if you need to assign SystemUser's to the ManagerId field, then you should assign the SystemUsers manually with a "Derived Column".