Metadata-based Dynamic ETLs
summary
A report generator product that captures source, destination and transformation rules in a metadata store (SQL Server) and a configuration UI generates runtime ETLs by emitting SSIS packages via Business Intelligence Markup Language. The generator turns a 660 line raw SSIS XML into roughly 30 lines of BML, produces per‑client packages on signup, and runs in production. Lessons include preferring flat target schemas, using ETL for batch not row processing, and keeping BML templates logic free.
AI-written summary · about
chapters (23)
- 0:00Introduction and key takeaways
- 1:06Classic ETL implementation challenges
- 3:59Scaling with manual ETL packages
- 5:22Metadata-based approach introduction
- 6:49Dynamic ETL generation concept
- 7:32Metadata store design
- 9:22Complex SSIS XML structure problem
- 10:03Introducing BML abstraction layer
- 11:30About BML and its libraries
- 12:11Integrating BML into ETL generator
- 13:14Final architecture overview
- 14:00Lessons learned from implementation
- 16:29Key lessons recap
- 17:34BML usage scenarios beyond SSIS
- 18:25Product demo and configuration UI
- 22:52Q&A on BML and SSIS generation
- 24:52BML IDE tools and generation process
- 25:58Product architecture deep dive
- 27:47Automated report generation workflow
- 28:41Dashboard and error tracking
- 29:09Supported data formats and integrations
- 29:30Version compatibility and deployment
- 30:11Closing and Q&A invitation
related talks
45mThe Metadata Driven Data Warehouse
15mNext Gen ETL Generators
55mThe ELT pattern in action with DBT and SQLMesh
16mEvolution of Extract-Transform-Load (ETL) processes towards data product pipelines
16mDemocratizing Data Pipelines: Introducing the AI-Powered ETL Assistant
39mWhat if SQL was back in fashion?
39mLeaving ETLs Behind: Moving from a ‘Code Less’ Solution to a ‘Code Full’ Approach
50mFrom SSIS to Synapse to Fabric - A Data Architects Case Study