Multiple worksheets using flat file destination in ssis

Question

I am trying to create multiple worksheets in excel using ssis. I am using flat file destination for creating the excel file by changing the file extension to xls. I can successfully create multiple excel files but what i am looking to do is to create multiple excel files with multiple worksheets. Can anyone suggest a simple workaround for that. Thanks

Answer 1

You have to use Excel connection Manager / Excel Destination to achieve that, also you may need to utilize from Execute SQL Task to create Excel Worksheets if you cannot do that manually:

• SSIS: Dynamically Generate Excel Table/Sheet
• Export SQL Server Data to Multiple Excel Worksheets with Integration Services
• SSIS - SQL to Excel
• Export SQL Server Data to Multiple Excel Worksheets using SQL Server Integration Services

Answer 2

It is not possible to create multiple worksheets using the Flat File Destination. This can only be done using the Excel Destination, using the technique shown in the linked question above.