If you use a data transformation task to populate the excel spreadsheet, your destination table name is your sheet name in excel.

You can create an Execute SQL Task to perform the drop table (drop sheetname) from your excel datasource. Note: close the spreadsheet before executing this

You then create the sheet with an Execute SQL Task to create the table (sheetname). You can get the code for this by going to your transformation destination tab and selecting create (it will pop up a window with the create table script). Copy this and paste it into your Execute SQL Task.

Connect the tasks with workflow and you are ready to roll.

Very good question! I actually have a similar job running. The business need in our case is to append data and I am using the export into Excel spreadsheet. Thanks everyone for your explanations, now I better understand why and how it works.

Yelena