Migrate SSIS packages to Aurora PostgreSQL – Part 1

When you migrate your databases from Microsoft SQL Server to Amazon Aurora PostgreSQL-Compatible Edition, one of the more complex challenges you face is migrating SQL Server Integration Services (SSIS) packages. SSIS is deeply embedded in many enterprise data architectures, handling ETL (Extract, Transform, Load) workflows, data transformations, file operations, email notifications, and scheduled job orchestration. Unlike schema or data migration, SSIS packages represent business logic and operational workflows that don’t have a one-to-one equivalent in the PostgreSQL ecosystem.

You might have hundreds or even thousands of SSIS packages accumulated over years of development. These packages vary widely in complexity, ranging from simple table-to-table data movements to sophisticated multi-step workflows involving Script Tasks, custom components, FTP operations, and complex control flow logic.

This is a two-part series. In this post, we show you how to filter, categorize, and simplify packages using native PostgreSQL features and extensions. In Part 2, we address complex orchestration patterns, including complex control flow items.

Solution overview

The solution follows a Filter → Categorize → Simplify → Modernize approach that helps you rationalize your SSIS portfolio before investing effort in migration:

1. Filter: Query the SSIS database catalog to identify which packages are active and in use. Remove disabled packages, packages that haven’t executed in months, and deprecated workflows. In some cases, this step alone typically reduces the migration scope by 30–50 percent.
2. Categorize: Classify the remaining packages by their primary function (data movement, transformation, file operations, notifications, and so on) and complexity level. This determines which migration path is appropriate for each package.
3. Simplify: For packages that perform straightforward operations (scheduled SQL execution, simple data movements between tables, basic cleanup jobs), convert them directly to PostgreSQL-native solutions using pg_cron, PL/pgSQL stored procedures, and Aurora PostgreSQL extensions such as aws_s3 and aws_lambda.
4. Modernize: For complex packages involving external system integration, multi-step orchestration, file processing, or API calls, migrate to AWS cloud-native services such as AWS Step Functions, AWS Lambda, Amazon Simple Storage Service (Amazon S3), and Amazon EventBridge.

This approach helps you invest modernization effort where it’s truly needed, while using PostgreSQL’s native scheduling and procedural capabilities for simpler workloads.

Architecture

The following diagram illustrates the end-to-end architecture for migrating SSIS packages to Aurora PostgreSQL and AWS native services. We show how different AWS services work together to replicate SSIS packages in Aurora PostgreSQL.

Figure 1: End-to-end architecture mapping SSIS components to Aurora PostgreSQL and AWS services

Prerequisites

Before beginning the migration process, make sure that the following are in place:

1. An AWS account with sufficient permissions to launch the resources necessary for this solution.
2. Completed schema conversion using AWS DMS Schema Conversion (AWS SC) and migrated data using AWS Database Migration Service (AWS DMS). This post uses the AdventureWorks2016 database set up on an Amazon Elastic Compute Cloud (Amazon EC2) SQL Server Enterprise Edition instance to illustrate the examples. The SSIS packages, stored procedures and additional artifacts were created to illustrate each pattern, and they aren’t part of the standard AdventureWorks installation. We provide a few code snippets to illustrate a few of the implementations.
3. Amazon Aurora PostgreSQL cluster: A running Amazon Relational Database Service (Amazon RDS)/Aurora PostgreSQL-Compatible Edition cluster (version 17.x or later recommended) with appropriate instance sizing for your workload. The tests were carried out on Aurora engine version 17.7. Make sure that the following extensions are enabled. Query the pg_extension view to check if the following extensions are installed.

CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS aws_s3 CASCADE;
CREATE EXTENSION IF NOT EXISTS aws_lambda CASCADE;

4. Database User. This post connects to Aurora PostgreSQL as the postgres admin user for simplicity and stores those credentials in the Secrets Manager secret the Lambda functions use. For a least-privilege production setup, replace it with a dedicated role that has only the required grants and update the secret to point at that role.
5. An Amazon Simple Storage Service (Amazon S3) bucket where the file exports will be stored.
6. An AWS Identity and Access Management (IAM) role associated with the Aurora PostgreSQL cluster to access the S3 bucket for exporting data. Please follow the principle of least privilege.
7. Virtual private cloud (VPC) networking: NAT Gateway or VPC endpoints for S3, AWS Lambda, and SNS access.

Solution walkthrough

The following steps walk you through migrating SSIS packages from SQL Server to Aurora PostgreSQL.

Step 1: Filter

The first step is to query the SSISDB catalog to determine which packages are in use. Many enterprises discover that a significant portion of their SSIS packages are no longer active. Execute the following query against your source(tested against SQL server 2019 version) SQL Server’s SSISDB catalog to get execution history:

USE SSISDB;
SELECT
f.name AS folder_name,
p.name AS project_name,
pkg.name AS package_name,
COUNT(e.execution_id) AS total_executions,
MAX(e.start_time) AS last_execution_time,
SUM(CASE WHEN e.status = 7 THEN 1 ELSE 0 END) AS successful_runs,
SUM(CASE WHEN e.status = 4 THEN 1 ELSE 0 END) AS failed_runs
FROM catalog.packages pkg
INNER JOIN catalog.projects p ON pkg.project_id = p.project_id
INNER JOIN catalog.folders f ON p.folder_id = f.folder_id
LEFT JOIN catalog.executions e
ON e.package_name = pkg.name
AND e.project_name = p.name
AND e.folder_name = f.name
GROUP BY f.name, p.name, pkg.name
ORDER BY last_execution_time DESC;

Additionally, check for packages tied to disabled SQL Server Agent jobs:

SELECT
j.name AS job_name,
js.step_name,
js.subsystem,
js.command,
j.enabled AS job_enabled,
CASE j.enabled WHEN 1 THEN 'Active' ELSE 'Disabled' END AS status
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE js.subsystem = 'SSIS'
OR js.command LIKE '%dtexec%'
OR js.command LIKE '%catalog.start_execution%'
OR js.command LIKE '%.dtsx%'
ORDER BY j.enabled DESC, j.name;

Note: The following table is guidance that can be tailored to meet your team’s requirements.

Step 2: Categorize

After you filter down to active packages, categorize each by its primary function and complexity. This determines the appropriate migration target. Use the following table as a guide to classify each active package into a migration category based on its primary SSIS pattern.

Step 3: Simplify

For packages categorized as Low or Medium complexity, convert them directly to PostgreSQL-native implementations. In this step, we walk you through four representative examples covering the most common SSIS patterns for low and medium complexity packages.

3a. Replacing Execute SQL Task with pg_cron schedule

SSIS Pattern: An Execute SQL Task that runs a stored procedure or SQL statement on a daily or hourly schedule through SQL Server Agent. The following package contains three Execute SQL tasks in sequence:

• Cleanup: Truncate the daily aggregation staging table.
• Aggregate: Run Stored procedure to summarize sales by territory.
• Verify: Check row counts and totals.

Figure 2: SSIS control flow with three sequential Execute SQL tasks for cleanup, aggregation, and verification

Aurora PostgreSQL equivalent:

On Aurora PostgreSQL, this maps directly to a PL/pgSQL stored procedure scheduled by the pg_cron extension. pg_cron runs inside the database engine, eliminating the need for an external scheduler. The procedure contains the same business logic, and cron expressions provide the same scheduling flexibility as SQL Server Agent (daily, hourly, weekly, and more). This is typically a line-for-line conversion with minimal refactoring.

CREATE OR REPLACE PROCEDURE analytics.usp_aggregate_daily_sales(
IN p_run_date date DEFAULT NULL::date)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_run_date DATE;
v_row_count INTEGER;
BEGIN
-- Default to yesterday if no date provided
v_run_date := COALESCE(p_run_date, CURRENT_DATE - INTERVAL '1 day');
-- Delete existing data for idempotency
DELETE FROM analytics.daily_sales_by_territory WHERE sale_date = v_run_date;
-- Aggregate sales by territory
INSERT INTO analytics.daily_sales_by_territory
(sale_date, territory_id, territory_name, total_orders, total_subtotal, total_tax, total_freight, total_due)
SELECT
soh.orderdate::DATE AS sale_date,
st.territoryid,
st.name AS territory_name,
COUNT(soh.salesorderid) AS total_orders,
SUM(soh.subtotal) AS total_subtotal,
SUM(soh.taxamt) AS total_tax,
SUM(soh.freight) AS total_freight,
SUM(soh.subtotal + soh.taxamt + soh.freight) AS total_due
FROM sales.salesorderheader soh
INNER JOIN sales.salesterritory st ON soh.territoryid = st.territoryid
WHERE soh.orderdate::DATE = v_run_date
GROUP BY soh.orderdate::DATE, st.territoryid, st.name;
GET DIAGNOSTICS v_row_count = ROW_COUNT;
RAISE NOTICE 'Aggregated % territory records for %', v_row_count, v_run_date;
END;

Schedule the procedure with pg_cron to replace the SQL Server Agent job.

Note: The pg_cron extension must be added to the ‘shared_preload_libraries’ in the Aurora cluster parameter group, which requires a reboot.

SELECT cron.schedule(
'daily-sales-aggregation',
'0 2 * * *',
'CALL analytics.usp_aggregate_daily_sales()'
);
UPDATE cron.job SET database = 'dmsdb' WHERE jobname = 'daily-sales-aggregation';

To verify, check the cron job to confirm it runs on the schedule that was set on the SQL Server Agent. In the following example, the job ran successfully at 2:00 A.M.

postgres=> select * from cron.job;
-[ RECORD 1 ]-------------------------------------
---
jobid | 1
schedule | 0 2 * * *
command | CALL analytics.usp_aggregate_daily_sale
s()
nodename | localhost
nodeport | 5432
database | dmsdb
username | postgres
active | t
jobname | daily-sales-aggregation
postgres=> select * from cron.job_run_details;
-[ RECORD 1 ]--+----------------------------------
---------------------------
jobid | 1
runid | 1
job_pid | 22523
database | dmsdb
username | postgres
command | CALL analytics.usp_aggregate_daily_sales()
status | succeeded
return_message | CALL
start_time | 2026-06-24 02:00:00.008086+00
end_time | 2026-06-24 02:00:00.025298+00

3b. Replacing Data Flow Tasks with PL/pgSQL

SSIS Pattern: A Data Flow Task that reads from a source table, applies transformations (Derived Column, Lookup, Conditional Split), and writes to a destination table. The following package contains a Data Flow Task with the following components:

• OLE DB Source: Reads from Sales.SalesOrderDetail.
• Lookup (Product): Joins ‘Production.Product’ for product name.
• Lookup (Category): Joins Production.ProductSubcategory and Production.ProductCategory.
• Derived Column: Computes OrderTier (ENTERPRISE / PREMIUM / STANDARD based on line total).
• Conditional Split: Routes rows where LineTotal > 1000 to HighValueOrders, remainder to StandardOrders.

Figure 3: SSIS Data Flow Task with lookups, a derived column, and a conditional split

Aurora PostgreSQL equivalent:

On Aurora PostgreSQL, this entire pipeline collapses into a single PL/pgSQL procedure using standard SQL constructs: JOIN replaces Lookup transforms, CASE expressions replace Derived Columns, and WHERE clauses replace Conditional Splits. The result is more concise, easier to version control, and executes as a single set-based operation rather than a row-by-row pipeline — often with better performance on large datasets.

CREATE OR REPLACE PROCEDURE analytics.usp_transform_order_details(
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
v_high_count INTEGER;
v_standard_count INTEGER;
BEGIN
TRUNCATE TABLE analytics.high_value_orders;
TRUNCATE TABLE analytics.standard_orders;
-- High Value Orders (Conditional Split: linetotal > 1000)
INSERT INTO analytics.high_value_orders
(salesorderid, salesorderdetailid, productid, product_name, category,
orderqty, unitprice, linetotal, order_tier)
SELECT
sod.salesorderid, sod.salesorderdetailid, sod.productid,
p.name AS product_name,
pc.name AS category,
sod.orderqty, sod.unitprice,
sod.linetotal,
CASE
WHEN sod.linetotal > 10000 THEN 'ENTERPRISE'
WHEN sod.linetotal > 5000 THEN 'PREMIUM'
ELSE 'STANDARD'
END AS order_tier
FROM sales.salesorderdetail sod
INNER JOIN production.product p ON sod.productid = p.productid
LEFT JOIN production.productsubcategory psc ON p.productsubcategoryid = psc.productsubcategoryid
LEFT JOIN production.productcategory pc ON psc.productcategoryid = pc.productcategoryid
WHERE sod.linetotal > 1000;
GET DIAGNOSTICS v_high_count = ROW_COUNT;
-- Standard Orders (linetotal <= 1000)
INSERT INTO analytics.standard_orders
(salesorderid, salesorderdetailid, productid, product_name, category,
orderqty, unitprice, linetotal, order_tier)
SELECT
sod.salesorderid, sod.salesorderdetailid, sod.productid,
p.name, pc.name, sod.orderqty, sod.unitprice,
sod.linetotal,
CASE WHEN sod.linetotal > 10000 THEN 'ENTERPRISE'
WHEN sod.linetotal > 5000 THEN 'PREMIUM' ELSE 'STANDARD' END
FROM sales.salesorderdetail sod
INNER JOIN production.product p ON sod.productid = p.productid
LEFT JOIN production.productsubcategory psc ON p.productsubcategoryid = psc.productsubcategoryid
LEFT JOIN production.productcategory pc ON psc.productcategoryid = pc.productcategoryid
WHERE sod.linetotal <= 1000;
GET DIAGNOSTICS v_standard_count = ROW_COUNT;
RAISE NOTICE 'High Value Orders: %, Standard Orders: %', v_high_count, v_standard_count;
END;

To verify, run the stored procedure on Aurora PostgreSQL and confirm the row counts match your source system.

dmsdb=> SELECT 'HighValue' AS dest, COUNT(*) AS rows FROM analytics.high_value_orders
dmsdb-> UNION ALL SELECT 'Standard', COUNT(*) FROM analytics.standard_orders;
-[ RECORD 1 ]---
dest | HighValue
rows | 32101
-[ RECORD 2 ]---
dest | Standard
rows | 89216

3c. Replacing file export with the aws_s3 extension

SSIS Pattern: A Data Flow Task that exports query results to a flat file (CSV) on a file share. The following package contains:

• Expression Task: To define the path for Exports.
• File System Task: To Create Export Directory.
• Data Flow Task: Writes CSV file to a network share.

Figure 4: SSIS control flow that prepares the export path and directory

Figure 5: SSIS Data Flow Task writing the CSV file to a network share

Aurora PostgreSQL equivalent:

On Aurora PostgreSQL, the aws_s3 extension provides query_export_to_s3() which exports any SQL query directly to Amazon S3 in CSV, TSV, or text format in a single function call. This removes the need for file shares, local disk management, and file cleanup jobs. S3 provides durable, versioned storage with built-in lifecycle policies, and the exported files are immediately accessible to downstream consumers like Amazon Athena, AWS Glue, or Amazon Quick Sight.

SELECT * FROM aws_s3.query_export_to_s3(
'SELECT
p.productid,
p.name AS product_name,
p.productnumber,
pc.name AS category,
psc.name AS subcategory,
p.listprice,
p.standardcost,
p.listprice - p.standardcost AS margin,
CASE WHEN p.sellenddate IS NULL THEN ''Active'' ELSE ''Discontinued'' END AS status,
pi.quantity AS stock_quantity
FROM production.product p
LEFT JOIN production.productsubcategory psc ON p.productsubcategoryid = psc.productsubcategoryid
LEFT JOIN production.productcategory pc ON psc.productcategoryid = pc.productcategoryid
LEFT JOIN (
SELECT productid, SUM(quantity) AS quantity
FROM production.productinventory GROUP BY productid
) pi ON p.productid = pi.productid
WHERE p.listprice > 0
ORDER BY pc.name, psc.name, p.name',
aws_commons.create_s3_uri(
'bucket-xxxxxxxxxxxx-us-east-2-dmss3-target',
'blog-examples/exports/product_catalog.csv',
'us-east-2'
),
options := 'FORMAT CSV, HEADER TRUE'
);
-[ RECORD 1 ]--+------
rows_uploaded | 304
files_uploaded | 1
bytes_uploaded | 29518

The output confirms that the record count matches the source. You can also schedule this export using pg_cron. The following example runs weekly every Monday at 6:00 A.M.

SELECT cron.schedule(
'weekly-product-export',
'0 6 * * 1',
$$SELECT * FROM aws_s3.query_export_to_s3(
'SELECT p.productid, p.name AS product_name, p.productnumber,
pc.name AS category, psc.name AS subcategory,
p.listprice, p.standardcost,
p.listprice - p.standardcost AS margin,
CASE WHEN p.sellenddate IS NULL THEN ''Active'' ELSE ''Discontinued'' END AS status,
pi.quantity AS stock_quantity
FROM production.product p
LEFT JOIN production.productsubcategory psc ON p.productsubcategoryid = psc.productsubcategoryid
LEFT JOIN production.productcategory pc ON psc.productcategoryid = pc.productcategoryid
LEFT JOIN (SELECT productid, SUM(quantity) AS quantity
FROM production.productinventory GROUP BY productid) pi
ON p.productid = pi.productid
WHERE p.listprice > 0
ORDER BY pc.name, psc.name, p.name',
aws_commons.create_s3_uri(''bucket-723044707697-us-east-2-dmss3-target'',
''blog-examples/exports/product_catalog.csv'', ''us-east-2''),
options := ''FORMAT CSV, HEADER TRUE'')$$
);

3d. Replacing email notifications with the aws_lambda extension

SSIS Pattern: A Send Mail Task that sends a report or alert by email upon job completion or failure. The following package contains:

• Execute SQL Task: Queries products below reorder point.
• Send Mail Task: Sends alert email through SMTP with product details.

Figure 6: SSIS package with an Execute SQL Task and Send Mail Task for low-inventory alerts

Aurora PostgreSQL equivalent:

On Aurora PostgreSQL, the aws_lambda extension invokes an AWS Lambda function that publishes to an Amazon Simple Notification Service (Amazon SNS) topic. SNS handles subscriber management, delivery retries, and multi-channel notifications (email, SMS, HTTP endpoints). This decouples the database from mail infrastructure entirely. The PL/pgSQL function simply passes a JSON payload, and the Lambda formats and delivers the notification. Adding new recipients or notification channels requires no database changes.

CREATE OR REPLACE FUNCTION analytics.send_inventory_alert(
)
RETURNS text
LANGUAGE 'plpgsql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
v_payload JSONB;
v_response TEXT;
v_products JSONB;
BEGIN
SELECT jsonb_agg(jsonb_build_object(
'product_name', p.name,
'reorder_point', p.reorderpoint,
'current_stock', COALESCE(pi.total_qty, 0),
'shortfall', p.reorderpoint - COALESCE(pi.total_qty, 0)
))
INTO v_products
FROM production.product p
LEFT JOIN (
SELECT productid, SUM(quantity) AS total_qty
FROM production.productinventory
GROUP BY productid
) pi ON p.productid = pi.productid
WHERE COALESCE(pi.total_qty, 0) < p.reorderpoint
AND p.finishedgoodsflag = 1;
v_payload := jsonb_build_object(
'subject', 'ALERT: Low Inventory - Products Below Reorder Point',
'message', 'The following products have inventory below their reorder points.',
'products', COALESCE(v_products, '[]'::jsonb)
);
SELECT payload INTO v_response
FROM aws_lambda.invoke(
aws_commons.create_lambda_function_arn(
'ssis-blog-inventory-notification',
'us-east-2'
),
v_payload,
'RequestResponse'
);
RETURN v_response;
END;
$BODY$;

To verify, run the following function call and confirm a successful response:

dmsdb=> SELECT analytics.send_inventory_alert();
send_inventory_alert
-------------------------------------------------------------------------------------------------
{"messageId": "b95fea16-7213-5329-9c05-88a1368286a1", "statusCode": 200, "productsAlerted": 77}
(1 row)

Schedule the alert using pg_cron:

SELECT cron.schedule(
'daily-inventory-check',
'0 7 * * *',
$$SELECT analytics.send_inventory_alert()$$
);

Summary

In this post, we demonstrate a systematic approach to migrating SSIS packages to Amazon Aurora PostgreSQL using native features and AWS extensions. By filtering, categorizing, and simplifying, you can migrate common patterns without requiring external orchestration tools. In Part 2, we cover the Modernize phase where we tackle complex packages that cannot be simplified with database-native features alone. We show you how to decompose these packages into AWS Step Functions state machines, with AWS Lambda functions for compute steps and native AWS SDK integrations for service calls.

About the authors