Migrate SSIS packages to Aurora PostgreSQL – Part 2

In Part 1, we handled extract-transform-load patterns that mapped cleanly to native PostgreSQL features and simple invocations of AWS services such as Amazon Simple Storage Service (Amazon S3) and AWS Lambda.

In this post, we address the complex multi-step SSIS packages that rely on orchestration, error handling, conditional branching, and parallel execution. These packages use precedence constraints, event handlers, and sequence containers that do not map to a single Lambda function. Instead, we use AWS Step Functions to replicate the control flow logic that SSIS provides.

By the end of this post, you will understand how to architect a Step Functions state machine replacing a 5-task SSIS package, scheduled through Amazon EventBridge, with complete error handling and notifications.

When to use Step Functions

Step Functions is a serverless orchestration service that coordinates multiple AWS services into workflows defined as state machines. A state machine is a directed graph of states where each state performs a unit of work such as invoke a Lambda function, making a choice, running tasks in parallel, or waiting for input and transitions to the next based on the outcome. You define the workflow in JSON using the Amazon States Language (ASL), specifying states, transitions, retry policies, and error handling. Not every SSIS package requires orchestration. For heavy data transformation workloads including millions of rows, complex joins across multiple sources, and Spark-based processing, consider AWS Glue. Many migrations use both: Glue for data-intensive ETL and Step Functions to orchestrate the Glue Jobs alongside other tasks.

Prerequisites

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

1. An AWS account with sufficient permissions to launch the resources necessary for this solution.
2. A Lambda execution role with the AWS managed policy AWSLambdaVPCAccessExecutionRole, secretsmanager:GetSecretValue on the Amazon Aurora PostgreSQL secret and sns:Publish on the Amazon Simple Notification Service (SNS) topic.
3. A Step Functions execution role granting lambda:InvokeFunction on the pipeline functions.
4. An Amazon EventBridge Scheduler role granting states:StartExecution on the target state machine.
5. Database user. Like Part 1, this post connects to Aurora PostgreSQL as the postgres admin user for simplicity and stores those credentials in the AWS 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.
6. An SNS topic for success and failure notifications if you want to receive alerts.
7. An Amazon S3 bucket for housing the artifacts created by the Step Functions.

Solution walkthrough

The following architecture diagram shows how we use different AWS services to modernize SSIS packages.

Figure 1: Architecture for modernizing complex SSIS packages with Step Functions and Lambda

In Part 1, we introduced you to the 4-step process and covered steps 1–3. We will now dive deep into the Modernize step.

Step 4: Modernize

The following examples walk you through migrating SSIS packages from SQL Server to Aurora PostgreSQL when your SSIS packages use multiple Execute SQL tasks chained with SSIS features such as precedence constraints, conditional expressions, or For Each Loop containers.

4a. Complex control flow using Step Functions

SSIS Pattern: When SSIS packages involve multi-step orchestration with conditional branching, error handling across steps, retry logic, and parallel execution, a simple stored procedure is no longer sufficient. AWS Step Functions provides a state machine that visually models the same control flow patterns: sequential steps, choice states (replacing precedence constraint expressions), catch/retry blocks (replacing event handlers), and parallel states (replacing Sequence Containers). Each step executes as a Lambda function that connects to Aurora PostgreSQL, and the state machine manages the flow, error handling, and notifications. Step Functions also provides built-in execution history, visual debugging, and Amazon CloudWatch integration.

The following package implements a vendor purchase order pipeline and contains five Execute SQL tasks:

• Extract Purchase Orders: Extracts pending purchase orders from the staging table.
• Validate Data Integrity: Checks for orphaned vendors (vendor IDs without matching master records) and negative amounts.
• Transform and Load: Aggregates validated data into the VendorPurchaseSummary table, producing summaries for vendors.
• Notify Success: Sends a success notification by email.
• Error Handler: If any step fails, execution jumps to the Notify Failure task.

Figure 2: SSIS vendor purchase order package with five Execute SQL tasks and an error handler

Step Functions state machine:

We replace the SSIS Control Flow with an AWS Step Functions state machine. Each Execute SQL Task becomes a Lambda invocation, precedence constraints become state transitions (Next), the validation check becomes a Choice state (equivalent to an expression-based constraint), and the error path becomes Catch blocks. A single Lambda function handles all steps: it receives a step parameter and executes the appropriate SQL against Aurora PostgreSQL. The state machine orchestrates the sequence, handles retry (with exponential backoff), and routes failures to a notification state.

Note: A State machine doesn’t provide retries, error routing or notifications out of the box. You define which failures route to a notification state. In the following state machine, we configure retry with exponential backoff on the Extract step ‘Retry’ block with ‘BackoffRate: 2.0’, failure routing through ‘Catch’ blocks on each task state, and a ‘Choice’ state that evaluates the validation result to decide whether to proceed or fail.

Figure 3: Step Functions state machine replacing the SSIS control flow with retries and catch blocks

State machine definition

The following state machine is a Standard Step Functions workflow that replaces the SSIS package orchestrating a vendor purchase order pipeline through five states that all call a single Lambda function with a different step parameter. It starts by extracting pending purchase orders (with a retry policy of up to two attempts on failure), validates the data, then uses a Choice state to check whether to branch to transform-and-load on success or to a shared failure notification otherwise. Every task also has a Catch block routing any error to NotifyFailure reproducing SSIS success/failure precedence constraints and event handlers.

{
  "Comment": "Replaces SSIS Package: Example_ComplexControlFlow.dtsx",
  "StartAt": "ExtractPurchaseOrders",
  "States": {
    "CheckValidation": {
      "Choices": [
        {
          "BooleanEquals": true,
          "Next": "TransformAndLoad",
          "Variable": "$.validateResult.isValid"
        }
      ],
      "Default": "NotifyFailure",
      "Type": "Choice"
    },
    "ExtractPurchaseOrders": {
      "Catch": [
        {
          "ErrorEquals": [
            "States.ALL"
          ],
          "Next": "NotifyFailure"
        }
      ],
      "Next": "ValidateData",
      "Parameters": {
        "step": "extract"
      },
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "ResultPath": "$.extractResult",
      "Retry": [
        {
          "BackoffRate": 2,
          "ErrorEquals": [
            "States.TaskFailed"
          ],
          "IntervalSeconds": 30,
          "MaxAttempts": 2
        }
      ],
      "Type": "Task"
    },
    "NotifyFailure": {
      "End": true,
      "Parameters": {
        "message": "The vendor purchase pipeline failed. Check CloudWatch logs for details.",
        "step": "notify",
        "subject": "FAILED: Vendor Purchase Pipeline"
      },
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Type": "Task"
    },
    "NotifySuccess": {
      "End": true,
      "Parameters": {
        "message.$": "States.Format('Pipeline completed. Vendors loaded: {}', $.loadResult.rowsLoaded)",
        "step": "notify",
        "subject": "SUCCESS: Vendor Purchase Pipeline Completed"
      },
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Type": "Task"
    },
    "TransformAndLoad": {
      "Catch": [
        {
          "ErrorEquals": [
            "States.ALL"
          ],
          "Next": "NotifyFailure"
        }
      ],
      "Next": "NotifySuccess",
      "Parameters": {
        "step": "load"
      },
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "ResultPath": "$.loadResult",
      "Type": "Task"
    },
    "ValidateData": {
      "Catch": [
        {
          "ErrorEquals": [
            "States.ALL"
          ],
          "Next": "NotifyFailure"
        }
      ],
      "Next": "CheckValidation",
      "Parameters": {
        "step": "validate"
      },
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "ResultPath": "$.validateResult",
      "Type": "Task"
    }
  }
}

This single Lambda function handles all pipeline steps, connecting to Aurora PostgreSQL through the psycopg2 layer and retrieving database credentials securely from AWS Secrets Manager. Note: Depending on the runtime of the Lambda function you may need to increase the timeout

import json, os, boto3, psycopg2

def get_connection():
    client = boto3.client('secretsmanager', region_name=os.environ.get('AWS_REGION', 'us-east-2'))
    secret = json.loads(client.get_secret_value(SecretId=os.environ['SECRET_ARN'])['SecretString'])
    return psycopg2.connect(
        host=secret['host'], port=secret['port'],
        dbname=secret['dbname'], user=secret['username'],
        password=secret['password'], sslmode='prefer'
    )

def lambda_handler(event, context):
    step = event.get('step')
    if step == 'extract':
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("SELECT COUNT(*) FROM purchasing.purchaseorderheader WHERE status IN (1,2)")
        count = cur.fetchone()[0]
        conn.close()
        return {'step': 'extract', 'orderCount': count, 'status': 'SUCCESS'}
    elif step == 'validate':
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("""SELECT COUNT(*) FROM purchasing.purchaseorderheader poh
LEFT JOIN purchasing.vendor v ON poh.vendorid = v.businessentityid
WHERE v.businessentityid IS NULL""")
        orphans = cur.fetchone()[0]
        cur.execute("SELECT COUNT(*) FROM purchasing.purchaseorderheader WHERE subtotal < 0")
        negatives = cur.fetchone()[0]
        conn.close()
        is_valid = (orphans == 0 and negatives == 0)
        return {'step': 'validate', 'isValid': is_valid, 'orphanedVendors': orphans, 'negativeAmounts': negatives}
    elif step == 'load':
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("CALL analytics.usp_load_vendor_summary()")
        conn.commit()
        cur.execute("SELECT COUNT(*) FROM analytics.vendor_purchase_summary")
        rows = cur.fetchone()[0]
        conn.close()
        return {'step': 'load', 'rowsLoaded': rows, 'status': 'SUCCESS'}
    elif step == 'notify':
        sns = boto3.client('sns', region_name=os.environ.get('AWS_REGION', 'us-east-2'))
        sns.publish(
            TopicArn=os.environ['SNS_TOPIC_ARN'],
            Subject=event.get('subject', 'Pipeline Notification')[:100],
            Message=str(event.get('message', 'Pipeline completed'))
        )
        return {'step': 'notify', 'status': 'SUCCESS'}
    elif step == 'list_vendors':
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("""SELECT DISTINCT v.businessentityid AS vendor_id, v.name AS vendor_name
FROM purchasing.vendor v
INNER JOIN purchasing.purchaseorderheader poh ON v.businessentityid = poh.vendorid
ORDER BY v.name""")
        vendors = [{'vendor_id': row[0], 'vendor_name': row[1]} for row in cur.fetchall()]
        conn.close()
        return {'step': 'list_vendors', 'vendors': vendors, 'vendorCount': len(vendors)}
    elif step == 'export_vendor':
        vendor_id = event.get('vendor_id')
        conn = get_connection()
        cur = conn.cursor()
        cur.execute("SELECT analytics.export_vendor_report(%s)", (vendor_id,))
        result = cur.fetchone()[0]
        conn.commit()
        conn.close()
        return {'step': 'export_vendor', 'vendor_id': vendor_id, 'result': result, 'status': 'SUCCESS'}
    else:
        raise ValueError(f"Unknown step: {step}")

With the control flow migrated, we now have a 5-step vendor purchase order pipeline with sequential steps, a validation choice gate, and error handling replacing SSIS precedence constraints and event handlers with orchestrated Lambda invocations against Aurora PostgreSQL.

4b. Foreach Loop Container – Step Functions Map state

SSIS Pattern: SSIS For Each Loop Containers iterate sequentially over a collection such as files in a directory, rows in a result set or items in a variable. On AWS, the Step Functions Map State processes the same collection in parallel with configurable concurrency.

• Get Vendor List: Reads from ‘Purchasing.Vendor’ for all vendors that have at least one purchase order. Returns a full result set and stores it in the VendorList Object variable.
• CreateVendorsExportDirectory: Create directory for Exports.
• Foreach Vendor: Iterates over each row in the VendorList dataset.
• Export Vendor to CSV: Generates vendor purchase order report.
• Log All Exports Complete: Fires after the loop finishes all the iterations. Logs a completion message confirming all vendor reports were exported.

Figure 4: SSIS For Each Loop Container that iterates over vendors and exports reports

Step Functions Map state:

On the AWS side, the Foreach Loop becomes a Step Functions Map state. First, a Lambda invocation lists all vendors (equivalent to the Get Vendor List task). Then the Map state iterates over the resulting array, invoking a Lambda for each vendor to export their report to S3. Additionally, map state processes concurrently by setting MaxConcurrency to 10 . Each iteration includes automatic retry on failure. After all vendors are processed, a final state sends an Amazon SNS notification confirming completion.

Figure 5: Step Functions Map state iterating over vendors to export reports concurrently

State machine definition

{
  "Comment": "Replaces SSIS Foreach Loop Container - iterates over vendors and exports reports",
  "StartAt": "ListVendors",
  "States": {
    "ListVendors": {
      "Type": "Task",
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Parameters": {
        "step": "list_vendors"
      },
      "ResultPath": "$.listResult",
      "Next": "ExportEachVendor"
    },
    "ExportEachVendor": {
      "Type": "Map",
      "ItemsPath": "$.listResult.vendors",
      "MaxConcurrency": 10,
      "ItemProcessor": {
        "ProcessorConfig": {
          "Mode": "INLINE"
        },
        "StartAt": "ExportVendorReport",
        "States": {
          "ExportVendorReport": {
            "Type": "Task",
            "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
            "Parameters": {
              "step": "export_vendor",
              "vendor_id.$": "$.vendor_id"
            },
            "End": true,
            "Retry": [
              {
                "ErrorEquals": [
                  "States.TaskFailed"
                ],
                "IntervalSeconds": 5,
                "MaxAttempts": 2
              }
            ]
          }
        }
      },
      "ResultPath": "$.exportResults",
      "Next": "NotifyComplete"
    },
    "NotifyComplete": {
      "Type": "Task",
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Parameters": {
        "step": "notify",
        "subject": "SUCCESS: Vendor Reports Exported",
        "message.$": "States.Format('All vendor reports exported. Vendors processed: {}', $.listResult.vendorCount)"
      },
      "End": true
    }
  }
}

With the control flow migrated, we converted a sequential per-vendor report export loop into a parallel Map State, exporting vendor reports to S3 concurrently thereby enabling faster processing with built in per item retries.

4c. Script Task – Lambda with Parallel state

SSIS Pattern: Unlike Execute SQL Tasks that run queries directly, SSIS Script Tasks are embedded C#/VB.NET that can call external APIs, parse files or apply logic that SQL can’t express. On AWS, this maps to a Lambda function (the API call) combined with a Step Functions Parallel state (the conditional branching). The Lambda replaces the C# code, and the Parallel state replaces the multiple precedence constraints that fan out from the Script Task. The following package contains:

• Call Credit Check API: Simulates calling an external vendor credit check REST API. Categorizes them into Approved, Rejected or Review Required categories.
• Process Approved Vendors, Reject Vendors, Flag Vendors for Review (In Parallel): Processes Approved, Rejected and Flagged for Review vendors for downstream processing.
• Send Summary Notification: Waits for all three parallel tasks above to complete and reports the final summary.

Figure 6: SSIS Script Task calling a credit check API with parallel approve, review, and reject paths

Step Functions with API Gateway + Parallel state:

Many SSIS Script Tasks reach outside the database to call REST APIs for credit bureaus, address-validation services, or internal microservices using custom C# HttpClient code embedded in the package. When you move this logic to AWS, you not only replace the C# code with a Lambda function, but you also gain the opportunity to expose that external call as a managed, secured endpoint. Amazon API Gateway is a fully managed service for creating, publishing, and securing APIs at scale. It handles request routing, throttling, authorization, and TLS termination, and integrates natively with Lambda as a backend. So instead of scattering HTTP-call logic across packages, you centralize it behind a single, governed endpoint. Critically, API Gateway supports private endpoints that are reachable only from within your virtual private cloud (VPC) through an interface VPC endpoint, meaning the service is never exposed to the public internet. The C# Script Task calling HttpClient is replaced by a Lambda that makes an HTTP POST to a private API Gateway endpoint. The API Gateway hosts a backend Lambda that acts as the “credit check service”, querying Aurora PostgreSQL for vendor credit ratings and returning a JSON response with vendor classifications. The pipeline Lambda receives this response, stores the results in Aurora PostgreSQL, and the Step Functions Parallel state then fans out into three concurrent branches (Approved, Review, Rejected), each writing to their respective tables. All branches must complete before the final notification state executes (fan-in).

Figure 7: Step Functions Parallel state fanning out to approved, review, and rejected branches

Step Functions state machine

{
  "Comment": "Replaces SSIS Script Task (API Call) + Conditional Branching",
  "StartAt": "CallCreditCheckAPI",
  "States": {
    "CallCreditCheckAPI": {
      "Type": "Task",
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Parameters": {
        "step": "credit_check_api"
      },
      "ResultPath": "$.apiResult",
      "Next": "ProcessAllPaths",
      "Retry": [
        {
          "ErrorEquals": [
            "States.TaskFailed"
          ],
          "IntervalSeconds": 10,
          "MaxAttempts": 2
        }
      ]
    },
    "ProcessAllPaths": {
      "Type": "Parallel",
      "Branches": [
        {
          "StartAt": "ProcessApproved",
          "States": {
            "ProcessApproved": {
              "Type": "Task",
              "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
              "Parameters": {
                "step": "process_approved"
              },
              "End": true
            }
          }
        },
        {
          "StartAt": "FlagForReview",
          "States": {
            "FlagForReview": {
              "Type": "Task",
              "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
              "Parameters": {
                "step": "flag_review"
              },
              "End": true
            }
          }
        },
        {
          "StartAt": "RejectVendors",
          "States": {
            "RejectVendors": {
              "Type": "Task",
              "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
              "Parameters": {
                "step": "reject_vendors"
              },
              "End": true
            }
          }
        }
      ],
      "ResultPath": "$.branchResults",
      "Next": "SendSummary"
    },
    "SendSummary": {
      "Type": "Task",
      "Resource": "arn:aws:lambda:us-east-2:XXXXXXXXXXXX:function:ssis-blog-pipeline-step",
      "Parameters": {
        "step": "notify",
        "subject": "Vendor Credit Check Complete",
        "message.$": "States.Format('Credit check complete. Approved: {}, Review: {}, Rejected: {}', $.apiResult.approved, $.apiResult.review, $.apiResult.rejected)"
      },
      "End": true
    }
  }
}

With the control flow migrated, we replaced a custom C# API call with a Lambda calling a private API gateway credit check service, then fanned out into three parallel branches.

4d. Scheduling with Amazon EventBridge

SSIS packages are triggered by SQL Server Agent jobs on schedules (daily, weekly, hourly). On AWS, Amazon EventBridge replaces SQL Server Agent as the universal scheduler for Step Functions state machines. Amazon EventBridge Scheduler is a purpose-built, serverless scheduler that supports one schedule per target, native time-zone handling, flexible time windows, and built-in retry and dead-letter configuration. Each state machine can be scheduled independently with its own cron expression, matching the original Agent job cadence.

Note: Amazon EventBridge Scheduler assumes an IAM role to start each execution, so the role’s trust policy must allow the scheduler.amazonaws.com service principal. The role also needs states:StartExecution permission on the target state machines.

Each schedule is created with a single create-schedule command (Scheduler combines the rule and target that classic Amazon EventBridge split across put-rule + put-targets). –flexible-time-window is required. Set it to OFF for exact-time execution matching SQL Server Agent, or FLEXIBLE with a window to spread load.

# Schedule 1: Vendor Purchase Pipeline (daily at 2 AM UTC)
aws scheduler create-schedule \
--name vendor-purchase-pipeline-daily \
--schedule-expression "cron(0 2 * * ? *)" \
--schedule-expression-timezone "UTC" \
--flexible-time-window '{"Mode": "OFF"}' \
--target '{
"Arn": "arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-purchase-pipeline",
"RoleArn": "arn:aws:iam::XXXXXXXXXX:role/ssis-blog-eventbridge-sfn-role",
"Input": "{}"
}' \
--region us-east-2
# Schedule 2: Vendor Report Export (weekly Monday at 6 AM UTC)
aws scheduler create-schedule \
--name vendor-report-export-weekly \
--schedule-expression "cron(0 6 ? * MON *)" \
--schedule-expression-timezone "UTC" \
--flexible-time-window '{"Mode": "OFF"}' \
--target '{
"Arn": "arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-foreach-export",
"RoleArn": "arn:aws:iam::XXXXXXXXXX:role/ssis-blog-eventbridge-sfn-role",
"Input": "{}"
}' \
--region us-east-2
# Schedule 3: Vendor Credit Check (daily at 7 AM UTC)
aws scheduler create-schedule \
--name vendor-credit-check-daily \
--schedule-expression "cron(0 7 * * ? *)" \
--schedule-expression-timezone "UTC" \
--flexible-time-window '{"Mode": "OFF"}' \
--target '{
"Arn": "arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-credit-check",
"RoleArn": "arn:aws:iam::XXXXXXXXXX:role/ssis-blog-eventbridge-sfn-role",
"Input": "{}"
}' \
--region us-east-2

With scheduling in place, your migration is complete. Each SSIS package that was previously triggered by SQL Server Agent now runs as a serverless Step Functions workflow, independently scheduled through Amazon EventBridge.

Considerations and limitations

Migrating SSIS packages to Step Functions and Lambda introduces tradeoffs that you should evaluate before committing to this approach.

Operational complexity:

• Additional services to monitor: Instead of checking SSISDB for execution history, you now need CloudWatch Logs, Step Functions execution history, Lambda metrics, and potentially X-Ray traces.
• AWS Identity and Access Management (IAM) roles and permissions: Each service-to-service interaction requires an IAM role.
• Team skill shift: There’s a learning curve involved wherein your team goes from needing SSIS/SQL Server expertise to needing familiarity with AWS services.

Debugging and troubleshooting:

• SSIS packages: This involves opening the packages, setting breakpoints, inspecting variables and watching data flow row-by-row with Data Viewers.
• Step Functions: This involves viewing the execution graph on the console, inspecting the JSON input and output at each state, checking the CloudWatch logs for Lambda errors.

Cleanup

To avoid incurring charges, clean up your resources. Complete the following steps:

1. Amazon EventBridge Scheduler schedules.

aws scheduler delete-schedule --name vendor-purchase-pipeline-daily --region us-east-2
aws scheduler delete-schedule --name vendor-report-export-weekly --region us-east-2
aws scheduler delete-schedule --name vendor-credit-check-daily --region us-east-2

2. Step Functions state machines.

aws stepfunctions delete-state-machine --state-machine-arn arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-purchase-pipeline --region us-east-2
aws stepfunctions delete-state-machine --state-machine-arn arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-foreach-export --region us-east-2
aws stepfunctions delete-state-machine --state-machine-arn arn:aws:states:us-east-2:XXXXXXXXXX:stateMachine:ssis-blog-vendor-credit-check --region us-east-2

3. Lambda Functions.

aws lambda delete-function --function-name ssis-blog-pipeline-step --region us-east-2
aws lambda delete-function --function-name ssis-blog-credit-check-service --region us-east-2
aws lambda delete-function --function-name ssis-blog-inventory-notification --region us-east-2

4. API gateways.

aws apigateway delete-rest-api --rest-api-id ipgva7ey71 --region us-east-2

5. SNS topic.

aws sns delete-topic --topic-arn arn:aws:sns:us-east-2:XXXXXXXXXX:ssis-blog-inventory-alerts --region us-east-2

6. CloudWatch log groups.

aws logs delete-log-group --log-group-name /aws/lambda/ssis-blog-pipeline-step --region us-east-2
aws logs delete-log-group --log-group-name /aws/lambda/ssis-blog-credit-check-service --region us-east-2
aws logs delete-log-group --log-group-name /aws/lambda/ssis-blog-inventory-notification --region us-east-2

7. IAM role associated with the Lambda function.

Lambda execution roles (each has the AWSLambdaVPCAccessExecutionRole managed policy attached):

# ssis-blog-pipeline-lambda-role
aws iam delete-role-policy --role-name ssis-blog-pipeline-lambda-role --policy-name SecretsAndSNS
aws iam detach-role-policy --role-name ssis-blog-pipeline-lambda-role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
aws iam delete-role --role-name ssis-blog-pipeline-lambda-role
# ssis-blog-credit-check-service-role
aws iam delete-role-policy --role-name ssis-blog-credit-check-service-role --policy-name SecretsAccess
aws iam detach-role-policy --role-name ssis-blog-credit-check-service-role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
aws iam delete-role --role-name ssis-blog-credit-check-service-role
# ssis-blog-lambda-notification-role
aws iam delete-role-policy --role-name ssis-blog-lambda-notification-role --policy-name SNSPublishPolicy
aws iam detach-role-policy --role-name ssis-blog-lambda-notification-role --policy-arn arn:aws:iam::aws:policy/service-role/AWSLambdaVPCAccessExecutionRole
aws iam delete-role --role-name ssis-blog-lambda-notification-role

8. Step Functions execution role (inline policy only, no managed policies):

aws iam delete-role-policy --role-name ssis-blog-stepfunctions-role --policy-name InvokeLambda
aws iam delete-role --role-name ssis-blog-stepfunctions-role

9. Amazon EventBridge Scheduler role (inline policy only, no managed policies):

aws iam delete-role-policy --role-name ssis-blog-eventbridge-sfn-role --policy-name InvokeStepFunctions
aws iam delete-role --role-name ssis-blog-eventbridge-sfn-role

10. If you have created an Aurora Cluster just for this blog then make sure to delete the cluster.

Summary

In this post, we demonstrated how you can migrate complex SSIS packages that rely on multi-step orchestration, conditional branching, error handling, and parallel execution to AWS Step Functions and Amazon Aurora PostgreSQL. We walked through four patterns: replacing SSIS Control Flow with a Step Functions state machine (4a), converting Foreach Loop Containers to Map states for parallel iteration (4b), migrating Script Tasks to Lambda functions with Parallel states (4c), and scheduling pipelines with Amazon EventBridge in place of SQL Server Agent (4d). Combined with the simple ETL patterns covered in Part 1, you now have a complete toolkit for migrating your SSIS workloads to AWS.

About the authors