Moving from SSIS to Azure Data Factory

A Specialty Insurance company engaged Triverus to assist them in an effort to move from their on-premises SQL Server Information Services (SSIS) infrastructure to Microsoft’s cloud-based Azure Data Factory (ADF) for their enterprise data and reporting solution. By changing data migration tools, the organization’s goal was to move company data in a more efficient and cost-effective manner, and with the addition of upgraded infrastructure the resulting solution would improve reliability, simplify support requirements, and speed time to market for enterprise reporting solutions.

Challenges

Migrating all of the organization’s data migration software to a new platform and toolset was a significant task. In their existing architecture, the extract, transform and load (ETL) design pattern was fairly common with SSIS providing orchestration, SQL Server Stored Procedures performing the transformations, and SQL Server Temporal Tables providing change data capture.

The new solution would need to perform these key activities adhering to best practices for the technologies comprising the target platform.

Results

The resulting solution using Azure Data Factory consisted of data-driven ETL flows within ADF for seven distinct sources which included over 100 tables plus flat file ingestion and retrieval of JSON messages from AWS S3.  The solution exceeded the performance and reliability of the previous on premises solution. It also utilized standard naming conventions to promote code reuse across environments, integrated with the Azure Key Vault service to manage source system credentials without embedding sensitive information in any of the code. Integrations to Azure Logic Apps allowed the pipelines to send email notifications for success/failure events, and integration with an Azure Function enabled the automated retrieval of data via a REST API.

How We Helped

The Triverus team conducted a series of proofs of concept to validate the functionality, scalability, and suitability-to-task of the Azure Data Factory solution.  The team defined the target data migration platform architecture and proceeded to develop a suite of new data pipelines allowing the company to completely migrate its nightly ETL processes from SSIS to Azure Data Factory.  

In addition, the team provided development and production support services for the legacy SSIS ETL infrastructure.  The team was able to leverage this knowledge of the legacy production processes as well as a better understanding of the existing pain points to not only migrate the functionality into ADF but to do so in a way that incorporated far more process resilience, error-checking and notifications.  At each stage of the release process, Triverus worked closely with the client’s QA team to create test scripts, perform data comparisons and research variances.