Excel Destination appends the excel file everytime a package is executed

i have an SSIS package that exports to an excel file. This works fine. the problem is that it appends the data instead of overwriting the file. Is there any way to overwrite the file like you can with a flat file? I have to email the file everyweek and don't want to have to clear it out manually. Any help would be appreciated

Perhaps you can use an Execute SQL statement with a delete statement before the data flow task. That way you delete existing rows before loading the new one.

Update: I just tried that and did not work:

[Execute SQL Task] Error: Executing the query "delete FROM [Sheet1$]" failed with the following error: "Deleting data in a linked table is not supported by this ISAM.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

So, another option may be to have an empty copy of the file that you copy/rename evry time the package run. You can use File system task in control flow for that.

|||

Pamela,

You could also use a Execute SQL task to create a new excel file on every execution of the package. I have posted an step by step guide on that:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html

I hope you find it helpful