This was a problem I racked my brains over for a while when I first started using SSIS. I found a few possible including using a File System task to copy a “Template” Excel file over the existing on, or generating unique files by appending the date. I simply wanted to overwrite the existing data just like the Flat File Destination gives you that option.
My workaround was as flows:
Create the Data flow the way you normally would, then go to the Control View Then add two Execute SQL Tasks before your data flow is called so it looks like this
Set both Execute SQL Tasks to use the Excel file
Set your first Execute SQL Task’s SQL Command to drop your table which is also known as the sheet name of your Excel file.
Repeat step 3 on the second Execute SQL Command this time create your tables.
If you are new to SSIS or looking for some more advanced help, one of my favorite books is Professional Microsoft SQL Server 2012 Integration Services
For those who faced with error on recreating table (“table already exists”) even after dropping on step-1:
Andrew (3/22/2013 post, thanks to him!) gave workable solution:
1) File System Task –> Drop Excel file
2) Execute SQL Task –> Create new table as explained in the main article
3) Data Flow Task –> Insert row from somewhere to Excel Destination.
This worked great!! Easiest way to get the table name is to go into the Excel destination dialog boxes and select create new. It will then show you the SQL it uses to create the table. Copy and paste that whole thing into your 2nd sql task, and just modify it to remove fields and say drop table for task 1. I was having issues and it was because it shows Excel_Destination by default but that is actually a space not an underline. Thank you!!