Producing multiple files in Automation Designer

Try this instead of an iterator.
Just use an export dataset action like you did before (when it created filenames like 1, 2, 3, 4)
You can specify your file names in the script as comments before each SQL like this:
(The file names can be anything, they don't have to correspond to the table that you are selecting from as in my example below)

-- filename: emp.sql
select * from emp;

-- filename: dept.sql
select * from dept;

-- filename: user_tables.sql
select * from user_tables;

Your iterator trick would probably work, but but it would be trickier to set up. Also, when using iterators, the action(s) t be driven by the iterator need to be nested under them, like this:

Not this

To nest an action under an iterator like that, you can just drag/drop it on top of the iterator.

See also Naming Excel sheets when Exporting data from SQL Queries - #15 by JohnDorlon
and Export dataset multiple queries