Cook Medical - Automated ETL Data Framework
MS SQL Server
Oracle
Azure DevOpsMDSBIMLAbout Cook Medical
Cook Medical is a global leader in healthcare innovation, delivering advanced medical solutions worldwide. The company needed a reliable, scalable approach to ingest, validate, and process large volumes of data received from multiple Oracle systems. MagnusMinds built an automated ETL framework using Master Data Services (MDS) for metadata management and BIML for dynamic SSIS package generation, streamlining the ingestion of Tier 1 and Tier 2 data files and preparing clean, structured outputs for API simulations.
MagnusMinds delivered a future-proof ETL architecture for Cook Medical that eliminated manual SSIS development. By leveraging MDS for metadata standardization and BIML for automated package generation, the solution reduced failures, improved data quality, and created a scalable foundation for long-term operational efficiency.
Our Approach
Challenge & Solution
The Challenge
Hundreds of Oracle files came with inconsistent formats, missing fields, and structural variations — even minor mismatches caused failures during SSIS package execution. Tier 2 processing relied heavily on the accuracy of Tier 1 loads, meaning any errors in Tier 1 propagated downstream and created widespread processing issues. Managing diverse file definitions was time-consuming, and configuring MDS manually for each new file caused delays in onboarding new data sources. Business rules, lookups, and relationship mappings required precise validations to ensure consistent and accurate transformations across all files.
Our Solution
We built a fully automated, metadata-driven ETL framework using Master Data Services (MDS) for consistent file definitions and seamless updates without manual intervention. Using BIML, SSIS packages were automatically generated and regenerated whenever file layouts changed, eliminating repetitive development work. We introduced robust pre-validation checks to detect formatting issues early, implemented strong transformation logic and lookup validations for accurate Tier 1 and Tier 2 loads, and added synonym-based stored procedures to prevent deployment conflicts. Together, these enhancements enabled consistent processing of large datasets, simplified onboarding of new file types, and delivered clean, API-ready outputs with comprehensive logging.
What We Built
Key Features
Metadata-driven ETL framework using MDS
BIML-based automatic SSIS package generation
Pre-validation checks for format and structure issues
Lookup validations and business rule transformation logic
Synonym-based stored procedures for deployment stability
Comprehensive logging for Tier 1 and Tier 2 processing
Impact
Results & Outcomes
Repetitive SSIS development eliminated via BIML auto-generation
Tier 1 and Tier 2 data loads fully automated
New data source onboarding simplified with MDS metadata
Clean, API-ready outputs with comprehensive error logging
Stack
Technologies Used
MS SQL Server
Oracle
Azure DevOpsMDSBIMLClient
Cook Medical
Industry
Healthcare / Medical Devices
Technologies
SSIS, MS SQL Server, Oracle…
Have a Similar Project?
Let's discuss your requirements and build something extraordinary together.