Export to Excel issue.

[demopro]

Hello,

I have a DTS that exports an Excel file. The issue is it appends the data to the bottom of the Excel worksheet. I need to have it truncate or delete and recreate the file whenever it is run.

Can someone point me in the direction to find the command / code example to complete this task.

Thanks,
Demopro

 

[dbomrrsm]

I have had the same problems in the past and never found a solution - the best I can come up with is to export the file as a csv - this overwrites and can be opened straight in excel anyway.

[blue]DBomrrsm[/blue]

 

[JayKusch]

Give this a look:

http://support.microsoft.com/kb/319951

Thanks

J. Kusch

 

[yksvaan]

To tell shortly what kb advices to do, you can do it with Export Wizard and check the two options of transform: Create Destination table and Drop and create destination table.

What is evident after examining the created package, is that Excel connection is a database connection with the file being the database, and not just a file. You can use Execute SQL tasks on that connection to perform some operations SQL-wise. Like dropping the existing table (example is exporting employee from pubs):
drop table `employee`

and creating table:

CREATE TABLE `employee` (
`emp_id` VarChar (9) ,
`fname` VarChar (20) ,
`minit` VarChar (1) ,
`lname` VarChar (30) ,
`job_id` Short ,
`job_lvl` Byte ,
`pub_id` VarChar (4) ,
`hire_date` DateTime
)

The .xls file must always exist, if you are designing from scratch and the file doesn't exist, you can create the file and table in the datapump task's destination tab.

You can't delete rows, it is not a permitted SQL statement on Excel database by this ISAM. So emptying is done with drop/create.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]