Azure Synapse pipelines for CRM data migration into Microsoft Dynamics

Our long-term client is one of the Top 5 Aged Care providers in Australia and has been serving people in need for over 160 years. They specialise in providing services to the vulnerable via a variety of not-for-profit activities including residential care, retirement living, community services, social housing, fundraising and more.

The Challenge

The business was moving from a bespoke Customer Relationship Management (CRM) system to a new Microsoft Dynamics 365 Field Service implementation and needed a way to migrate client and resourcing records into Dynamics. There were several scheduled go live dates and the mapping of existing bookings and resource scheduling had to be migrated to ensure a smooth transition for client visits with billing and care worker time sheeting.

The Solution

The client already had an extensive Azure implementation with AD, Logic Apps and Data Lake storage, so it was a natural fit to utilise the Azure Synapse pipeline approach for data migration. The existing CRM was an on-prem SQL Server, so we had to create a series of pipelines to manage various table records to be migrated.

Dynamics 365 is a natural fit with Azure Synapse as it allows for read-write access through the Dynamics connector which is not possible directly through an SSMS connection.

The team tested the process through DEV, UAT and then Production systems to ensure the process was robust. There were several go-live groups across many weeks and the Azure Synapse pipeline process assisted with keeping the original CRM and the new Dynamics CRM up to date throughout the project period.

The smarts of the pipeline can have logic to prevent double entry of records so the pipeline can be run multiple times to update incremental data through the project lifecycle. Some Power BI reports were created to monitor the variances between source and migrated data as a safeguard checking process.

Overall, the Azure Synapse pipeline experience gave the project team lots of flexibility and surety around data migration accuracy.

Testimonial

"Qualiticks rescued our project. The previous data mapping was underbaked. Qualitickswere able to come in and not only form the technical work, but advise on process, structure and architecture. We have a better product for their involvement. We would use Qualiticks again for similar work and projects (and in fact still am). The main thing we would have done with differently with Qualiticks is engage them sooner!"

Head of Client Services