I have successfully used SSIS to generate multiple Excel worksheets in the same Excel file where the columns all contained the same type of data. I was able to achieve this using suggestions from this link: https://social.msdn.microsoft.com/Forums/en-US/6f4a5b98-6373-4f7e-8d03-0876d0b4b611/loading-data-from-multiple-tables-to-multiple-sheets-of-excel-using-ssis?forum=sqlintegrationservices and which is elaborated on in this link: https://social.msdn.microsoft.com/Forums/en-US/2421d9aa-5347-4f24-a78b-da02eb644f5f/export-sql-server-table-into-multiple-sheets-in-excel?forum=sqlintegrationservices
For the new project I am working on, I am trying to export data from multiple SQL tables with different columns and data into multiple sheets in the same Excel workbook. Since the data is different for each table, the Dataflow Task in the example above doesn't appear to be dynamic to allow for the changes to columns and data conversions.
I know that this is possible using the SQL Server Import and Export Wizard but I am looking at over 200 tables. Instead, I have passed the desired table-names to an object variable in SSIS using the SELECT TABLE_NAME FROM information_schema.tables query. Perhaps there is a script that could deal with this. I am new to SSIS and although haven't used VB for several years, I am willing to use it or any other method to achieve the result.
Am I missing something? Is this procedure possible in SSIS?
