Migrate SSIS packages to Aurora PostgreSQL – Part 1
The blog post walks readers through a “Filter → Categorize → Simplify → Modernize” workflow that starts by querying the SSISDB catalog to surface active packages, then classifies them by function and complexity. Simple data‑movement jobs are rewritten as pg_cron‑scheduled PL/pgSQL procedures or COPY commands that leverage the aws_s3 extension, while more intricate flows are off‑loaded to Step Functions, Lambda, Glue, or ECS. The authors demonstrate the process with an AdventureWorks2016 instance on an EC2‑hosted SQL Server Enterprise edition, and they require Aurora PostgreSQL 17.x (tested on 17.7) with the pg_cron, aws_s3, and aws_lambda extensions enabled. By pruning packages that haven’t run in six months or that belong to disabled SQL Agent jobs, the guide claims teams can shave 30‑50 % off the migration workload before any code conversion begins.
The guidance reflects a broader industry push to replace heavyweight, on‑prem ETL tools with cloud‑native, event‑driven pipelines. Azure’s counterpart, Data Factory, already offers a lift‑and‑shift path for SSIS, but AWS is positioning Aurora’s extensibility and the surrounding serverless stack as a lower‑cost, more flexible alternative. The use of familiar PostgreSQL constructs (pg_cron, PL/pgSQL) lowers the learning curve for DBAs, while the optional migration to Step Functions or Lambda lets organizations adopt a micro‑services style without abandoning their relational data layer. This hybrid approach mirrors the “modern data stack” trend where data warehouses serve as both storage and orchestration hubs.
Practically, the recipe forces teams to confront hidden technical debt in their SSIS portfolios and to decide which jobs merit a full re‑architecture versus a direct SQL rewrite. Risks include under‑estimating the effort required to replicate custom Script Tasks in Lambda, managing IAM permissions for Secrets Manager and S3 access, and ensuring that pg_cron’s scheduler meets the same SLA guarantees as SQL Agent. Early adopters should monitor execution latency on Aurora when using COPY + aws_s3 for bulk loads and validate that Step Functions’ state‑machine limits align with their existing control‑flow complexity.
Key Takeaways
Filtering SSISDB for inactive or failed packages can cut the migration scope by up to half before any code changes.
Simple table‑to‑table moves are best rebuilt with pg_cron‑driven PL/pgSQL procedures and the aws_s3 extension.
Complex orchestrations should be refactored into AWS Step Functions, Lambda, or Glue, leveraging Aurora’s native extensions for data access.
Successful migration demands careful IAM role design, least‑privilege Secrets Manager usage, and performance testing of Aurora‑based ETL workloads.
About the Source
This analysis is based on reporting by AWS Database Blog. Here is a short excerpt for context:
Migrating SQL Server Integration Services (SSIS) packages is one of the more complex parts of moving from SQL Server to Amazon Aurora PostgreSQL. In Part 1 of this two-part series, learn how to filter, categorize, and simplify your SSIS portfolio using native PostgreSQL features, pg_cron, and the aws_s3 and aws_lambda extensions.Read the original at AWS Database Blog
