I have a table that I update each morning via a schedule DTS package.
I have another DTS package that does a simple SELECT * on that table and I
want to dump it to Excel.

The problem is that instead of overwriting Excel each morning, the DTS
package appends the new data to the previous day's data - which is creating
useless junk.

Anyone know how I can have the DTS package just overwrite the file instead
of appending to the file?
--
randy volters

keep a blank excel file , copy it to original excel file using
aFileSystemObject in an activex script task
as follows:

Set oFSO = CreateObject("Scripting.FileSystemObject")
oFSO.CopyFile sSourceFile, sDestinationFile
Set oFSO = Nothing



"randyvolters" <randyvolters (AT) discussions (DOT) microsoft.com> wrote

"randyvolters" <randyvolters (AT) discussions (DOT) microsoft.com> wrote


create a blank copy of the excel, delete the original version, & replace
with the template before you start the DTS

e.g.

c:\output.xls <-- the original one
c:\output_template.xls <-- the blank one

create a batch file as follows:
----
del c:\output.xls
copy c:\output_template.xls c:\output.xls
----

Run the batch file from the DTS befor eyou export into the Excel

randyvolters wrote:
I have two methods of doing this:

1. Keep a template workbook with empty worksheet in another folder. The worksheet must have been already created (but
empty) in the template workbook so you get the correct data types. FTP the template from the template folder to the
production folder. Insert your data into the worksheet.

2. If the workbook can't be overwritten by a template workbook, I drop the table and create it again, then insert the
data. Just the one sheet is affected. The others remain current. The new worksheet will appear at the end of the
workbook.

I have one workbook where a sheet of data underlies a pivot table which is used for a chart. I can drop and re-create
the underlying data table without affecting the pivot table and the chart will have the latest data. Excel never
realizes that the worksheet has been deleted and re-created.

When I use the template method, Excel seems to sometimes forget what data types are in the cells. When I insert an
integer into a number format cell, Excel gives me the green triangle and thinks the data is text. Dropping and
re-creating the table does not have this effect.

Ed

Ed,

Just how do you drop the Excel worksheet before you create it?

Wodger

"Ed Enstrom" wrote:

<snip>...</snip>

Wodger,

I believe you can use SQL Tasks within the DTS package.

To remove existing data, create a SQL task connected to the Excel workbook.
Use the "DROP TABLE" command (if you are using a sheet name, remember to
enclose the "table" name in square brackets).

To re-create the table, create another SQL Task and use the CREATE TABLE
command - easiest way is to copy it from the SQL generated by the [Create]
button in the Destination tab of the data pump task.

This seems to work for me.

Good Luck
"Wodger" wrote: