SSIS File SystemTask to create an Excel File dynamically from a SQL Query or Table during Runtime

How to create an Excel File dynamically from a SQL Query or Table Data using File System Task during Runtime in SSIS 
Suppose If you want to create an Excel File dynamically every time during run time and update the data from a SQL Table to this Excel File using SSIS, you can do it simply by using the File System Task and Data Flow Task with Execute SQL Tasks as follows :

Phase-1 : Add a Data Flow Task to Control Flow :

Phase-2 : Create the 3 connections ; one for OLEDB Source, one for Excel Destination and another for the File System task :

1.OLEDB Souce : The connection for the Source table from which we want to update the data to excel destination. 
Eg : MyDbCon

2.Excel Destination : The connection for the Destination Excel file in which we want to update the data from Source table.
Eg : EmpDtls

3.File Connection : The connection for the same Destination Excel file which we want to delete and create every time during run time .

Eg : FileEmpDtls

Phase-3 : Go inside the Data flow task and add the OLE DB Source:
In the OLE DB Source Editor choose the Connection Manager and Table or View 
(You can choose SQL Command as well )

Phase-4 : Next add the Excel Destination and connect to the OLE DB Source:

Phase-5 : Next in the Excel Destination Editor do the following things:
1.Select Excel Connection Manager that you created in the begining
2.At the Name of the Excel Sheet , Don't choose any thing, simply click the Next button.
3.Click OK on any Warning/Info message box popups
4.Now it will generate an SQL Script based on your Source Table/Query as follows:

CREATE TABLE `EmpData` (
    `Emp_Id` INT,
    `Emp_Name` NVARCHAR(255),
    `Gender` NVARCHAR(255),
    `Job_Id` INT,
    `Dept_Id` INT,
    `Joining_Date` DATE,
    `Basic_Sal` DOUBLE PRECISION,
    `Dept_Name` NVARCHAR(255)
                       )
Here you can also select only the no.of columns you want in destination.

5. You can change the Table name as you wish, which will be created in your Excel Destination as Tab,once you click OK.

Phase-6 : Next select the Name of Excel sheet which is created dynamically from Source Table , and do the column Mappings...

Now if you want to check , go to your Excel Destination file where you can see the same table created with structure as shown below :

Now the Data flow task is ready.

Phase-7 : Next we have to add the Execute SQL Task and connect that with Data Flow task.
Next set the Connection Type property as Excel then define the SQL Query to create an excel file during run time before executing the Data Flow task.

Inside the Control Flow .....

                                       

Phase-8 : Next we have to add the File System Task and connect that with  Execute SQL Task.

Next in the File System Task Editor set the Operation property as Delete File to delete the file before the Execute SQL Task create an excel file during run time.

Final Phase-9 : Now the whole SSIS Task is ready and it works as follows :
1.First the File System Task will delete the destination File.
2. Next Execute SQL Task will create a Destination Excel file during run time.
3.Finally Data Flow task , designed in the beginning, will execute the inside defined Execute SQL Task that transfers the data to Excel Destination.

Output in Destination file :

Please Note :
Every time a new file will be created with the same name by deleting the existing one.
You can use the Multi-Cast/Conditional Split Transformations here in the Data Flow to transfer the same Input with different Select list to Multiple Destinations/Sheets, based on your Conditions and requirement.

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------