Moving SSIS and SSRS workloads to AWS: options on Amazon RDS for SQL Server and EC2

#1. Where they can run

RDS keeps it managed, with limits. EC2 gives full control.

#2. SSIS on RDS

• Add the SSIS option to the instance's option group; the instance must be joined to a domain (AWS Managed AD) because packages run under Windows authentication.
• Deploy projects to SSISDB on RDS with Visual Studio or the deployment wizard using a Windows login.
• Package files and flat files go through S3 integration (rds_download_from_s3, rds_upload_to_s3 stored procedures) to D:\S3\ on the instance.
• Schedule with SQL Agent on RDS.

Limits: no custom third-party components that need installation on the OS, only the supported package features and connection types; heavy SSIS load competes with the database for CPU and memory.

#3. SSRS on RDS

• Add the SSRS option (it hosts the report server databases on the instance and exposes the web portal on a port you choose, over HTTPS with a certificate).
• Migrate reports (.rdl), shared data sources and subscriptions using rs.exe scripts or the PowerShell ReportingServicesTools module:

Install-Module ReportingServicesTools
Out-RsFolderContent -ReportServerUri http://ssrs-onprem/ReportServer -RsFolder /Finance -Destination C:\rs\Finance -Recurse
Write-RsFolderContent -ReportServerUri https://rds-sql-prod.aws.corp.local:8443/ReportServer -Path C:\rs\Finance -RsFolder /Finance -Recurse

• Recreate security (folder roles) and data source credentials; test subscriptions (email via a configured SMTP).

#4. When to use EC2 instead

• Custom SSIS components, script tasks with external libraries, or large package estates.
• SSRS with custom extensions, very high report load, or Power BI Report Server.
• Separate the ETL/reporting server from the database server so they don't compete (the database itself can still be RDS).

Migrate the SSIS catalog to EC2 with SSISDB backup/restore (and the master key), and SSRS by backing up/restoring ReportServer and ReportServerTempDB plus the encryption key:

rskeymgmt -e -f C:\rs\rskey.snk -p <password>       :: export on old server
rskeymgmt -a -f C:\rs\rskey.snk -p <password>       :: apply on new server

#5. Modernise later

Use the move to catalogue packages and reports: retire unused ones (often 30–50%), and plan the rest for Glue/dbt or Power BI in a later wave.

Tip

Back up the SSRS encryption key before you touch anything. Without it, stored credentials and subscriptions in the restored report server database can't be decrypted.