Once I need to write to a new Excel file and what I did was the following:
-Create a Excel connection manager to a file, if you need you can change the location and name of the file dynamically.
-At the start of the process I delete the file
-Then I create the file using the "Execute SQL Task" with following code using the Excel connection on the "Connection" property of the component:
CREATE TABLE `ExcelDestination` (
`Value` NUMERIC (18,4),