I attempt to export data from a query via DTS to an excel
spreadsheet.


When I select the option:
"Create Destination Table" and "Drop and Recreate
Destination Table", I find that whenever I run the DTS,
rows are appended to the Destination Table.


In this way, I just create a dummy Excel spreadsheet and I
select "Delete rows in Destination Table". I suppose that
it will delete all rows and replaced with the result of
DTS Select Statement. However, when I run the DTS, I get
the error message "Deleting data in a linked table is not
supported by this ISAM".


Your advice is sought. Should I use CSV file as output
file instead of Excel ?


Thanks

Stephen wrote:
I create a lot of reports using Excel spreadsheets. Here is how I do it.

I have two folders on the server, one named "Source" and the other "Reports".
In the Source folder are blank templates for the various reports. Each of these
has a single worksheet, containing the company name and the name of the report.

In the DTS package I have the following steps:
1. FTP task to overwrite the report from Source folder to Reports folder
2. Execute SQL task to create the table in the workbook in the Reports folder.
This will create a second worksheet in the workbook. Its name will be the
name of the table.
3. Data transformation task to pump the data from the database query to the
worksheet.
4. Mail the workbook to the recipients.

Works fine for me.

HTH,
Ed

Dear Ed,

Thank you for your advice. Finally I have followed your instruction and
created the DTS.

However, I would like to know is it possible to include 3 queries in the
stored procedure and match each one to a different worksheet ?

Besides, if I would like to use parameter input, is there any article I can
find relevant information ?

Thanks

"Ed Enstrom" <nospam (AT) invalid (DOT) net> wrote

Stephen wrote:
Create a connection to the workbook. Create three worksheets with Execute SQL
task. Each worksheet in the workbook corresponds to a table, just like in a
database. The name of the worksheet is the name of the table. Each query would
do inserts into a different table name, which puts the data on different worksheets.

Do you mean passing global parameters from DTS to the stored procedure?
Try this:
Global Variables and Stored Procedure Parameters
http://www.sqldts.com/default.aspx?234