ETL Script to dynamically map multiple EXECUTE SQL resultset to multiple tables (table name based on sql file provided)

Question

ETL Script to dynamically map multiple execute sql resultset to multiple tables (table name based on sql file provided)

I have a source folder with sql files ( I can put it up as stored procedures as well ) . I know how to loop and execute sql tasks in a foreach container. Now the part where I'm stuck is I need to use the final result set of each sql queries and shove it into a table with the same name as the sql file.

So, Folder -> script1.sql , script2.sql etc -> ETL -> goes to table script1, table script2 etc.

EDIT : Based on the comment made by Joe, I just want to say that I'm aware of using insert within a script but I need to insert it onto a table in a different server.And Linked servers are not the ideal solutions

Any psuedocode or link to tutorials will be extremely helpful . Thanks!

Answer 1

I would add the table creation to the script. It is probably the simplest way to do this. If your script is Select SomeField From Table1, you could change it to Select SomeField Into Table script1 From Table1. Then there is no need to map in SSIS which is not easy to do from my experience.