Exporting SQLServer data to multiple sheets of Excel file

There was a requirement in one of my recent projects to export data from table in SQLServer database to an Excel file. Though it sounds a straightforward one, there was a tricky part in it. The data has to go to separate sheets of the Excel file based on a column value. This blog explains the method I used for achieving the requirement.

The scenario can be illustrated by the below example.

Consider the case of Marklist table containing details of marks obtained by various students for different subjects. The table looks like below (for simplicity I'm including only a small amount of rows)

The requirement was to export this data to excel with each subject details going to separate sheet.
The package looks like below

The tasks used are
1. File System Task to copy Excel template from a shared location to actual directory. This is required for setting up of file metadata prior to sheet creation. The data will be copied into separate sheets within this file.
2. SQL Task to generate sheet list . This task will execute SQL script to get distinct list of subjects from table. This will then be stored in a object variable for use within the loop to create the sheets.
3. For Each Loop which loops through the subject list from the object variable
4. SQL task which will connect to excel file and create sheets based on dynamic CREATE TABLE statement as below by storing query in a variable (ExcelSQL)

The CREATE TABLE expression will make use of variable to pass Subject name from loop to create the sheet at runtime.
5. Data Flow Task to populate the created sheet with data for that subject. The Data Flow looks like below

The query will have Subject value passed as a parameter using the variable created inside the loop.
The destination will use dynamic sheet name by mapping to the same subject variable

On executing the package we will get excel generated as below

As you see the Excel will have separate sheets based on Subject names with each sheet having data for the subject.

6. The final file system task renames the template to set current date as the filename 

This approach can be applied to similar scenarios to get data exported to multiple sheets. Feel free to revert in case you need more clarification on this.