Skip to main content
Docs
Feature Requests Onboarding Trust Center Status Page

Community

Connector Improvement: Microsoft Dynamics 365 Finance and Operations - Change order of insert/delete

Answered
Nash Lindley User
  • Edited

In Dynamics datalake, a record on a table can be deleted and a new record can start using the same id. It is possible for delete and insert of new record to happen in the same set of transactions.

We noticed that when this occurs the Microsoft Dynamics 365 Finance and Operations connector tries inserting the new record before applying the delete to the old record which causes duplicate data. In our case, we have primary keys on our Sql Server destination tables and this causes a "primary key constraint or unique index violation" because the insert is processed before the delete.

To solve this issue, I propose updating this connector to process deletes before inserting new records. This would prevent the error from occurring.

Please sign in to leave a comment.

Comments

2 comments

Date Votes
  • Official comment
    Shivaprasad Mogili Fivetranner

    Hi Nash,

    Thanks for raising this request, and I have added the request to our connector improvement backlog for Microsoft Dynamics 365 Finance and Operations.

    To better understand your use case, could you share more details about how often you encounter this issue and what downstream implications it has for your team? Are there specific tables or transaction types where deletes before inserts are especially critical in your environment?

    We will keep the community updated on this thread as we make progress on this improvement.

    Thanks,
    Shiva

  • Nash Lindley User

    Hi Shiva,

    Thanks for your response. We run into this error almost daily and need to manually set the fivetran deleted flag of the old record to true to resolve the issue. 
    Until we set that deleted flag, the fivetran connector remains broken giving the "primary key contraint or unique index violation" error.  So we effectively stop receiving data entirely for a period of time. Let me know if there is any additional details I can provide.

    Thanks,

    Nash