Case study · P—13
SSIS Framework for ETL Pipelines
- Role
- Framework development · Senior Business Intelligence Developer
- Period
- November 2010 – April 2011
- Organisation
- LC Waikiki
- Stack
- SSIS, SQL Server
- Status
- Completed
In short
Metadata-driven SSIS ETL framework with dependency tracking, error handling, restartability and automated alerts, adopted by 80+ pipelines.
Context
LC Waikiki’s data warehouse has several layers and is loaded by SSIS (SQL Server Integration Services) pipelines.
Role: developed the framework, as Senior Business Intelligence Developer.
Problem
Every pipeline has the same needs: run in the right order, handle errors, restart after a failure and tell someone when something breaks. Without a shared framework, each pipeline has to solve these on its own.
Approach
A reusable, metadata-driven ETL framework:
- Dependency tracking: pipelines run in the right order.
- Error handling and restartability: a failed run can restart without starting over.
- Prioritization: urgent jobs can be put first.
- Operational metadata: run information is captured.
- Automated alerts: failures are reported.
Architecture and technology
SSIS on SQL Server. Pipeline behaviour is defined in metadata rather than hard-coded in each package, and the framework spans the layers of the warehouse.
Outcome
- Adopted by 80+ pipelines.
- Data consistency across the multi-layer warehouse.
Lessons
- Reuse is the measure here: with 80+ pipelines on one framework, dependency tracking, restartability and alerting come with each of them.
- The metadata-driven approach continued: in 2014–2017, 80+ SSIS jobs were refactored to incremental, metadata-driven loads, and the batch window went from 7 hours to under 2.