From Legacy SSIS to Cloud AWS Glue

At Xelops Technology, in 2025, we began a massive application refactoring and Information System overhaul, 4 years after the move-to-cloud. As we had many databases and applications to move to AWS cloud, we decided to leave the ETLs as is, with minor fixes.

In fact, even before the official launch of the ETLs migration, we had a recurring problem with a specific ETL. The main objective of this ETL is to handle new payments and synchronize between 2 distinct systems. Because the ETL was developed in 2013, processing a large volume of payments often caused it to run overnight or even time out. The jobs that depended on its execution were interrupted too. So many times, we had to manually check and rerun the job. 

Since we were already on AWS, we decided to tackle the migration from SSIS to the ETL service of AWS, Glue.  

Initially, we had 2 SSIS jobs, with a chmod script in between. As we read the requirements, it was obvious that one Glue job was enough to replace the 3 components.

First, reading the requirements was an absolute necessity to quantify the efforts needed and conceive the core elements of the job. 

Migration automated with SCT tool

First, we explored the AWS tool: Schema Conversion Tool (SCT: Converting SSIS to AWS Glue with AWS SCT - AWS Schema Conversion Tool) 

The tool offers various features: migrating database x to database y, migrating stored procedures, and migrating ETLs from SSIS, for example, to AWS Glue.

The output wasn’t great as the initial SSIS job was overengineered. To have a better output, the SSIS job had to be fixed first: simplification & aggregation of 2 jobs in 1. 

The SCT approach then was abandoned as it proved to require a lot of effort.

Manual migration starting with the SSIS jobs

As said before, the SSIS jobs were over-engineered, so it was hard to understand what the job was doing. Moreover, the data engineer needs to have SSIS knowledge to understand what the job was doing, debug, etc. Knowledge that wasn’t required for the future. 

This approach was then also abandoned.

Manual migration starting with the specifications

This approach was pretty straightforward. Understand the requirements: data mapping, tracing and logs, alerts, etc.

To develop the AWS Glue, we needed a data engineer with PySpark and Glue ecosystem knowledge. Knowledge that will help us in the upcoming overhaul of the ETL jobs. 

Furthermore, with the help of AI (Claude in our case), we only needed some prompts to generate a starting pyspark script, then tweak it to ensure it was optimized, and add the confidential information about our database and schema, etc.

The output was great. Not only have we reduced the job runtime from 8 hours to 5 - 10 minutes max, but we also migrated everything from FTPs, SSIS Windows Machines, to the AWS ecosystem. 

Since then, the job runs without failure and requires nearly zero maintenance and we have tackled the migration of the rest.

In conclusion

Migrating legacy ETL processes like those built in SSIS to modern cloud services such as AWS Glue goes beyond a simple technical conversion. It requires a deep understanding of business requirements, a thorough review of specifications, and investment in the right skill sets aligned with the new platform. As a result, this migration project allowed us to improve both performance and scalability, minimizing manual interventions and errors.