SSIS export Flat file based on each SQL Query in the table

I think you can do this using a C# script and a loop.

So, you would loop through each line in the result set you posted above, and set variables like SQLQuery, FilePath etc. from the results each time.

Within the loop you then execute the SQLQuery variable and return the results to an object variable in SSIS (we'll call that obj_ResultSet).

Still within the loop, you pass obj_ResultSet, and FilePath, and a FileName if you generate that anywhere, to a Script Task, which takes the data from the obj_ResultSet and outputs it to a file. I'm not a C# expert, so you're probably better off getting someone else to look at that bit for you, but I think you want to extract the DataTable object from your result set, then stream it to a new file using a StreamWriter object. I also have no idea about performance, but this is the main way I can think of that avoids the flat file connection manager issues anthony talks about.

You would want a FOR LOOP object which is set to the output of they query where it goes and pulls the information into variables for each job name and switches them on the fly.

The issue you will have is that the flat file connection manager must have the same metadata, so Table1 and Table2 must have the same columns of the same data types in the same order etc, otherwise you need to see if you can do dynamic metadata in BIML for example.

Either that or you have logic and multiple flatfiles and if the job is Execution1 go down one path and if the job is Job2 go down another, that then gets into an admin nightmare as more jobs is more paths and more changes and more likely something will break.

https://www.red-gate.com/simple-talk/sql/ssis/implementing-foreach-looping-logic-in-ssis/