I would still try using Automation. I would use a loopDataset activity.
The driving query would list the name of the name with schema reference. Something similar to what you are showing above.
Then use the loopdataset variable for that row to build your TOP 10 sql.
Then add Select to file as a child activity and set the sql as below.
select TOP 20 * from #Loop_data_1_SQL.SchemaTable#
The #Loop_data_1_SQL.SchemaTable# is replacement syntax and will be replaced with each row value for SchemaTable.
The Select file options should be to append a worksheet for every export and name the worksheet the name of the table. I attached a sample. Change the extension to "tas". You will need to change the connect info to run.
Top 20 rows.txt (15.6 KB)