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

excel

hy

I need to access data from an sql server 2005 database everytime i open an excel file to fill a list with employee names. if i import it once, new people won't be added, if i import it on event open true vba, it asks which dataset to use. is there a way you can use vba but don't need to make an connection manually eveytime.

Many thanks.
jens

hi jens,

you can add a vba form to excel

in the form you can place a button and named it refresh

when the user clik on the button thats the only time

you read new employee from sql server

regards,

joey