Redshift Serverless Zero-ETL options for RDS MS-SQL & S3, Redshift options for External API Get

Leveraging managed solutions wherever possible is certainly a good idea as frees you up from infrastructure heavy lifting and allows you to focus on achieving business goals. So you choosing Zero-ETL is a great decision. For sources not supported by Zero-ETL you can use DMS , example for MS-SQL. For S3 files in open format or table formats you can indeed leverage Redshift Spectrum to access the data in-place. And if your performance SLA's need faster access then you can consider S3 Auto-copy jobs to have the data automatically ingested into Redshift tables.

For #5 External API , I believe you will need to have some program that calls the API, obtains the data and decides what to do with it. Refer another re:Post Ingest data from a REST API and store into Redshift for handling this.