I am running a script file with 4 SQL queries, with 'Discard Output' and 'Prompt for Substitution Variables' selected. This runs correctly.
I follow that up with an Export Dataset action that references the same SQL file on the Dataset tab, with the Directory specified on the Options tab. This will produce the 4 files correctly, but will name them 1,2,3, and 4.
How can I get those named something more intuitive and related to the data? I tried using a file Iterator before the Export Dataset action, referencing a file I created with the 4 chosen file names, but this is not having any affect.
screenshot:
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
Perfect! thanks
