SQL Server DB Python Builder

Project completed in 7 times less weeks than originally estimated (86% time reduction)

Project Context

• Setting

  Data-specialized consultancy in Spain

• Client

  One of Spain's most prestigious universities

• The Challenge

  Build database structure with 350+ tables in allocated timeframe

• My Approach

  Automated the entire process with Python instead of manual creation

• Development

  Few days to build the automation script

• Task Impact

  7x faster completion than allocated project timeline: 86% time reduction

• Future Value

  Future changes done in seconds compared to weeks of manual work

About the Database Structure

Build a new database structure with 350+ tables that needed to be created manually, one by one, across 4 different layers:

• 1. SSIS Packages

  Extract data from Oracle to SQL Server

• 2. STG Layer

  SQL Server First layer: staging tables

• 3. ODS Layer

  SQL Server Second layer: operational data store

• 4. Stored Procedures

  Data transformations STG → ODS

Manual Time Requirements

350+ tables × 20 columns average = 7,000+ individual elements to create manually across 4 layers:

Per Table Estimates: (Work to do for each one of the +350 tables)

• SSIS Package

  45-60 minutes

• STG Table

  15-20 minutes

• ODS Table

  15-20 minutes

• Stored Procedure

  30-45 minutes

• Total per table

  ~2 hours

Additional Manual Process Challenges:

• Column name variations: Many tables have different naming conventions requiring manual checking
• Data type mismatches: Oracle to SQL Server type conversions need individual verification
• Human error risk: Repetitive manual tasks are highly prone to mistakes and inconsistencies
• Version control issues: Manual changes across 350+ tables create maintenance nightmares

The Solution Impact

My Python automation eliminated this extremely tedious and time consuming manual process. Instead of creating each table manually across all layers, the script generates everything automatically: SSIS packages with complex XML structures, SQL table definitions, and stored procedures with transformations—all in seconds rather than weeks, with zero human error risk.

Technical Challenges & Achievements

• 1. Complex SSIS Package Generation

  • Programmatically created SSIS packages for 350+ tables
  • Each package containing hundreds of lines of XML code
  • Automated weeks of manual effort into seconds of execution
  • Ensured precise XML structure adherence for SSIS functionality

• 2. Multi-layered Database Structure

  • Managed table creation across multiple database layers (STG, ODS)
  • Maintained data integrity and consistency across layers
  • Implemented complex transformations between layers

• 3. Cross-Database Compatibility

  • Translated Oracle-specific features to SQL Server equivalents
  • Handled different data types and constraints across platforms
  • Ensured data integrity across different database systems

Confidentiality: For confidentiality reasons, I can't provide the university name or exact time details, but both can be fact-checked during the recruitment process.