The client receives data in Excel format, processes it through SSIS packages, and stores it in a SQL Server 2016 database, with the pipeline running in production.

Business Challenges

The client's SSIS packages and data warehouse needed to move from SQL Server 2016 to SQL Server 2022 while the pipeline continued running in production.

Long-running package performance

A subset of SSIS packages were taking close to two hours to complete, adding latency to the client's Excel-to-warehouse data pipeline.

Continuity through cutover

The Excel-based ingestion process needed to keep running through the migration without disrupting production data loads.

Project Objectives

Geopits scoped the engagement to migrate every SSIS package from SQL Server 2016 to SQL Server 2022 and optimize the packages responsible for the longest execution times.

Key Goals:

Migrate all SSIS packages and the data warehouse to SQL Server 2022
Identify and resolve the indexing gaps driving the longest package runtimes
Reduce execution time on the highest-cost packages without changing their output

Solution Provided by Geopits

Geopits ran the engagement as a structured, four-stage process covering migration, deployment, index remediation, and package optimization.

Package Migration

Migrated all SSIS (.dtsx) packages from the SQL Server 2016 server to the new SQL Server 2022 server and updated all Connection Managers to point to the new environment.

Deployment & Validation

Deployed all SSIS packages to the new SQL Server 2022 server and supported the client in manually executing and validating every migrated package.

Index Remediation

Identified missing indexes and created them in the SQL Server 2022 database to close performance gaps.

Package Optimization

Optimized the highest-cost SSIS packages by adding appropriate indexes and replacing DELETE operations with TRUNCATE where applicable, significantly reducing execution time.

Results & Business Impact

The figures below reflect the scope and outcome of this migration and optimization engagement as reported by the client's team.

Capabilities

Before

After

SSIS packages

Running on the SQL Server 2016 environment

Migrated, deployed, and validated on SQL Server 2022

Package runtime

Some packages taking close to two hours to complete

Optimized through indexing and TRUNCATE-based logic to reduce execution time

Indexing

Missing indexes contributing to long-running packages

Missing indexes identified and created to close performance gaps

Data operations

DELETE-based logic used in applicable packages

Replaced with TRUNCATE where applicable to cut execution overhead

Key outcomes

100% of SSIS packages migrated

Directly represents moving the packages and data warehouse to SQL Server 2022

Long-running packages optimized

Packages that were taking close to two hours to complete were re-indexed and optimized, significantly reducing execution time.

Missing indexes identified and created

New indexes were added in the SQL Server 2022 database to close performance gaps found during the migration.

DELETE-to-TRUNCATE optimization applied

DELETE operations were replaced with TRUNCATE where applicable, cutting unnecessary execution overhead on high-cost packages.

Conclusion

Geopits migrated every SSIS package and the associated data warehouse from SQL Server 2016 to SQL Server 2022, and used that migration as an opportunity to resolve the indexing gaps and long-running package behavior the client had been carrying on the legacy platform. The client's SSIS pipeline now runs on SQL Server 2022 with the performance gains from indexing and TRUNCATE-based optimization built in.

Ready to Transform Your Data?

Geopits works alongside your team as a strategic partner, starting with stabilizing your current databases, then modernizing your data infrastructure, and ultimately helping you unlock the full potential of AI.

Book a Demo Learn More

170

Happy Clients so far

2100+

Databases Managed

142+

Successful Migrations

At Geopits, our mission is to empower businesses to achieve reliable, secure and high-performing database environments and to derive meaningful business insights.