I have been tasked to automate our reporting process from an old forest and trees format to using SSIS and SQL. The main goal is to automate hundreds of reports that get exported daily to excel. I need a way to dynamically pass or read in a query, execute it and then export the results to excel. I have a DB of the report names, report query locations (which are all in a .sql files), and the location the report needs to export to.
I can successfully read the query file and execute it using an Execute SQL Task and stuff the results in an object variable. One of the issues is getting the data out of the object and into excel. The second issue is getting SSIS to deal with the column names. I dont want to setup hundreds of packages and map the columns by hand to get this process to work. (Yes, they have to be in excel and not .csv format). The third issue I am running into is when I use a Data Task Flow with an OLE DB Source and pass in the SQL Query as a variable I am hitting the 4000 character limit on strings. I have also tried using the Script Component as a source but am unsure if you can/ how to dynamically create output rows.
Is this even possible?