Could anyone advise me how can sync data between SQL Server on premise to AWS using CDC or change tracking. I'm not sure,can I use SSIS for integration?
Thank you
The canonical AWS method for SQL Server-to-cloud CDC is Database Migration Services, it works with both self-managed SQL Server instances and RDS instances of SQL Server. Be aware it has some limitations and prerequisites for your SQL Server, so do an assessment up front that your scenario is applicable.
If you can't meet the prerequisites or constraints above, you can also consider using the popular open source tool Debezium, especially if you have to have a lot of custom hooks before or after the CDC, since it posts all the changes to a Kafka topic.
SSIS is an acceptable tool if you're only doing batch-level updates and don't need near-realtime replication and you want to fully control the synchronization, but there is a lot of overhead to developing and maintaining those types of packages. I wouldn't recommend it.