In this case SSIS is a good choice regardless of the complexity of the tranformation because the source and destination databases are going to be on different instances.

If you want to stay in T-SQL your choices become Linked Servers or xp_cmdshell. Personally I would continue down the path of implementing an SSIS Package to do the archiving.

In order to retrieve 'only the updated or new data' you will need to know something about each row that you can use to derive whether it was updated or new. And what about deletes?

Typical columns used to determine new or modified rows include 'created date' or 'last updated date' columns. Do you have one or both of those, and are they reliable?

A rock solid way to do this, although it introduces more DBA work, is to leverage either Change Tracking or Change Data Capture. Those two features work at the database-engine-level to track which rows have changed.