SSIS Developer – Guidewire Aurora to SQL Server Migration

Role: Senior SSIS Developer Duration: 3+ Months Work Location: Remote-Canada Job Description: The SSIS Developer will be responsible for designing, developing, and maintaining ETL pipelines to extract data from the Guidewire Aurora PostgreSQL database and load it into on-premises SQL Server databases. This role plays a critical part in a major Guidewire upgrade/migration initiative and involves building a nightly batch cycle, ensuring data accuracy through reconciliation processes, and validating end-to-end data movement. The ideal candidate has strong ETL, SQL, and data migration experience within insurance domain systems. Required Technical Skills ETL & Database • Strong hands-on experience with SSIS (SQL Server Integration Services). • Expertise in SQL Server (T-SQL, stored procedures, triggers, indexing, query optimization). • Experience extracting from PostgreSQL/Aurora using ODBC/OLEDB/.NET providers. • Experience with data migration, large datasets, and performance tuning. Guidewire Migration Knowledge (Preferred) • Familiarity with Guidewire Policy Center, Claim Center, or Billing Center data models. • Understanding of Guidewire Aurora database structures for PC/CC/BC. • Experience working on Guidewire version upgrades or cloud migrations is a plus. Other Skills • Experience with ETL job scheduling (SQL Server Agent, Control-M, Autosys, etc.). • Experience using Git, Azure DevOps, Jenkins, or other CI/CD tools. • Ability to write reconciliation scripts in SQL and/or Python (optional). • Strong understanding of data quality, audit, and compliance practices. Qualifications & Experience • 5–8 years of ETL development experience, with at least 3+ years in SSIS. • Prior experience in migration projects, preferably Guidewire or other insurance platforms. • Strong analytical skills and ability to investigate data anomalies. • Experience working in Agile/Scrum environments. • Bachelor’s degree in Computer Science, Information Systems, Engineering, or equivalent experience. Key Responsibilities 1. ETL Design & Development • Develop SSIS packages to extract data from Guidewire Aurora (PostgreSQL) and load it into SQL Server databases. • Design scalable and optimized ETL frameworks supporting incremental loads, full loads, and delta logic. • Configure secure connectivity between cloud Aurora DB and on-prem SQL Server. • Implement transformations, business rules, surrogate key management, and referential integrity through ETL. 2. Nightly Batch Cycle Setup • Design and orchestrate nightly ETL batch processes using SQL Server Agent or enterprise schedulers. • Implement error-handling, retry logic, logging, and alerting for batch jobs. • Monitor performance and optimize slow-running jobs to meet ETL SLAs. 3. Data Validation & Reconciliation • Conduct field-level and record-level reconciliation between Aurora and SQL Server tables. • Develop comparison scripts to validate counts, checksums/hashes, and business-critical attributes. • Identify mismatches, perform root-cause analysis, and coordinate with database and Guidewire functional teams to resolve issues. • Maintain audit trail, data lineage documentation, and reconciliation dashboards. 4. Support Guidewire Upgrade/Migration • Collaborate with Guidewire functional consultants to understand PC/CC/BC data models and changes introduced by the upgrade. • Perform data profiling to identify data gaps or transformation needs stemming from version differences. • Support parallel runs, cutover planning, and post-migration validation. • Ensure ETL processes align with Guidewire data model standards and release cycles. 5. Documentation & Delivery • Create detailed technical design documents (TDD), mapping sheets, and ETL flow diagrams. • Maintain SSIS source code in version control (Git/Azure DevOps). • Provide post-deployment support, including defect resolution, optimizations, and enhancements.