Overwriting an Excel file destination using SSIS
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 work around 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 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.
That’s it. Good luck.