All good suggestions. I like the template idea, seems the cleanest way to do it. I saw another method whereby you select a huge range of cells and clear the contents. OK if you can predict a range you'll never exceed.

The Excel destination task doesn't like you to delete the file as it creates the workbook during design then expects it to be there afterwords on subsequent executions.

There is a way to make it create a new one each time but I haven't tried it yet.

http://social.technet.microsoft.com/Forums/en-US/transactsql/thread/7f79cfde-2f1f-4cad-94f8-07397b998296/

I did try this, and it completed the Script task without error but didn't actually do anything other than open and close the spreadsheet.

OpenExcelWorkbook(FileName)

Try

_sheet = CType(_sheets(Tab1), Microsoft.Office.Interop.Excel.Worksheet)

_sheet.Select(Type.Missing)

_sheet.Delete()

Catch ex As Exception

MsgBox(ex.ToString)

CloseExcelWorkbook()

End Try

Without the Try/Catch it would leave the file in an open state and Excel would have to recover it if I tried to open it in the app.

There may be a way to do it, perhaps the interop assembly has some bug in this regard; it seems like a straightforward method. But I will likely go with templating and archiving.

Thanks all