Overview

WestSafe Insurance Group is an EU-based insurer running policy, claims, finance, and customer operations on separate systems. An on-premises SQL Server ran SSIS packages overnight that pulled data from those systems for reporting. Growing data volumes made the batch run even longer, and reports arrived a day late. Bacancy Technology modernized the client’s data management environment with Microsoft Fabric and Azure, replacing 94 legacy SSIS packages with Fabric Data Factory pipelines and bringing data from five source types into OneLake. We also implemented governed data layers, centralized lineage, and Power BI reporting to give teams faster access to trusted data. The modernization reduced nightly processing time by approximately 55%, cut manual reconciliation effort by 70%, and made reporting data available at the start of the business day.

Technologies Used

Microsoft Fabric
Microsoft Azure
Microsoft OneLake
Microsoft Purview
Power BI

Project Highlights

checkmark

Legacy SSIS workflows redesigned for Microsoft Fabric

checkmark

Five source types connected through Fabric Data Factory

checkmark

Data processing and transformation implemented across OneLake

checkmark

Azure-native governance for identity, secrets, lineage, & EU residency

The Challenges

1

SSIS packages processed policy, claims, finance, and customer data overnight. The packages ran on a fixed nightly schedule. They loaded sources turn by turn, then built the reporting tables from the combined data. Data volumes grew, new sources were added, and the load was still running in morning, holding up the reports analysts needed to start work.

2

The data came from five kinds of sources. Policy and claims records lived in SQL Server, finance data in a SAP-based ERP, and customer and broker data in Salesforce. Third-party feeds came over REST APIs, and external providers sent CSV and Excel files. SSIS handled the database sources, but the API and file-based sources required custom components.

3

Reporting numbers could not be traced back to source. The client held contractual GDPR and data residency obligations, and SSIS produced no lineage, so answering a question about a figure meant working back through the transformation manually. Classification of personal data depended on dev knowledge rather than anything recorded in platform.

4

The on-premises platform had reached its capacity limit. SQL Server could not scale further, so every new source competed for the same fixed compute. The developers who wrote the SSIS packages had left, and 94 packages held logic nobody had documented, so a change to one package could break the loads that depended on it.

Solutions by Bacancy

1

Bacancy Technology’s data engineers assessed all 94 SSIS packages, identified the workloads that were still in active use, and redesigned them as Fabric Data Factory pipelines rather than lifting the packages onto an integration runtime. We scheduled each pipeline against the reports it fed, so the loads ran separately instead of in one single queue for overnight load.

2

Our Microsoft Fabric experts connected each source with its matching Fabric connector, using database connectors for SQL Server and the SAP-based ERP, the Salesforce connector for CRM data, REST connectors for third-party feeds, and file connectors for the CSV and Excel datasets. Our Insurance IT experts ensured that policy, claims, finance, customer, and broker data retained its business context.

3

We built the governance layer on Microsoft Purview. Our data governance team catalogued the integrated data, classified personal data at column level, and mapped lineage from each Power BI report back to its source system. We configured access through Microsoft Entra ID, moved the connection credentials into Azure Key Vault, and deployed the platform in an Azure EU region to meet the residency requirement.

4

Our data migration team moved processing to Fabric capacity and structured the data in Bronze, Silver, and Gold layers in OneLake. We cleaned, standardized, deduplicated, and validated the data in Silver before applying the required transformations in Gold. We then served the Gold datasets through Fabric Warehouse and connected Power BI through Direct Lake for reporting.

Core Features

checkmark

Column-level classification of personal data across all integrated sources

checkmark

Native Fabric connectors for database, API, and file-based sources

checkmark

Report-level lineage from Power BI back to source system

checkmark

Fabric capacity monitoring for pipeline scheduling & query performance

No. of Resource

04

No. of Resource

Time Frame

June 2025 – January 2026

Time Frame

Project Snapshot

Dashboard for WestSafe Insurance Group

Outcomes

Overnight data processing time reduced by approximately 55%

Reporting data made available at the start of the business day

5 Data source types consolidated into OneLake

Report refresh latency down from 1s to 200 ms

Manual reconciliation effort brought down by 70%

Lineage traceable from report to source system for GDPR audit

Technical Stack

Source systems SAP-based ERPSalesforceREST APIsCSV/Excel
Legacy platforms SQL ServerSSIS
Cloud platforms Microsoft FabricMicrosoft Azure
Data ingestion & orchestration Fabric Data Factory
Storage OneLake
Data architecture Medallion (Bronze, Silver, Gold)
Serving layer Fabric Warehouse
BI/reporting Power BI
Data governance Microsoft Purview
IAM Microsoft Entra ID
Secrets management Azure Key Vault
Capacity Fabric Capacity

Experience With Bacancy

Optimize Your E-Commerce Workflow for Maximum Efficiency

iconWatch the video

Fintech Platform Scalability & Growth Optimization

iconWatch the video

Legacy Code Upgrades for Modern User Experience and Enhanced Data Analytics

iconWatch the video

Empowering Retail Marketing With Seamless User Experiences

iconWatch the video

Migrating To Laravel From Core PHP With Twilio Integration

iconWatch the video

Upgrading PHP Laravel version while scaling the Admin Dashboard

iconWatch the video

Optimizing CRM Performance With Data Management and Salesforce Integration

iconWatch the video

AI Solution Engine for Improved Performance and Error Handling

iconWatch the video

Enhancing Managed IT Services With Streamline Automation Process

iconWatch the video

SEO and Bulk Data Upload Solutions For An E-Commerce Healthcare

iconWatch the video

Elevate Your E-Commerce with Scalable UI and Enhanced Calculators

iconWatch the video

Simplifying Annuity Rate Management for Complex Calculations

iconWatch the video

Addressing Null Safety, Code Structure Issues, and Manual Deployment Issues

iconWatch the video

Optimize Task Management With Advanced Tracking

iconWatch the video

Revolutionize Applicant Tracking: Advanced Image Optimization Techniques

iconWatch the video

Cost-Effective Media Management Solution and Resolving Data Upload Issues

iconWatch the video

Enhance Your Management Service with User-Centric Innovations

iconWatch the video

2500+ Projects Experienced Innovation with Bacancy!

Get access to an experienced team of developers and engineers from Bacancy, handpicked to ace your goals. Kickstart within 48 hours, no-risk trial.

Book a 30 min call

14+

Years of Business Experience

1458+

Happy Customers

12+

Countries with Happy Customers

1050+

Agile Enabled Employees