Exporting SQL to Excel Hourly

First time here so please bear with me.

Set up a DTS package to export data to an excel sheet on an hourly
basis. Problem is, it keeps appending to the same excel sheet.

Any idea how to prevent that. All I want to accomplish is that every
hour, the latest data is in the excel sheet and the previous data is
deleted.

Thanks in advance!"Anthony" <anthony@.alfy.com> wrote in message
news:8ca54ab8.0406151028.65a8fbbb@.posting.google.c om...
> First time here so please bear with me.
> Set up a DTS package to export data to an excel sheet on an hourly
> basis. Problem is, it keeps appending to the same excel sheet.
> Any idea how to prevent that. All I want to accomplish is that every
> hour, the latest data is in the excel sheet and the previous data is
> deleted.
> Thanks in advance!

One solution could be to add an ActiveX or Execute Process task to simply
delete the existing XLS before exporting the data, so that you create it
again each time. This could be a problem if someone else has the XLS open at
that point, but presumably you've handled that already anyway.

Simon|||
Thanks Simon. Not to familiar with creating an ActiveX to do that. Any
idea how easy/hard it is? Maybe point me in the right direction?

Thanks again

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Here is another approach.
1.) create a sql agent job and schedule it to run hourly.

2.) first step deletes the old excel file (change type to cmdexec and
"DEL \\<path>\<name>.xls" in the command line.

3.) second step creates a new excel file by copying a template file to
the path and file name you want.

4.) create a stored procedure that creates a linked server to the excel
spreadsheet (see BOL for sp_addlinkedserver), then exports the data to
it.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi

These may be useful.
http://www.sqldts.com/default.aspx?292
http://www.sqldts.com/default.aspx?200
http://www.sqldts.com/default.aspx?245

If you need a specific template then you can copy an existing file and
rename it.

John

"Anthony Mehale" <anthony@.alfy.com> wrote in message
news:40cf49c7$0$25541$c397aba@.news.newsgroups.ws.. .
>
> Thanks Simon. Not to familiar with creating an ActiveX to do that. Any
> idea how easy/hard it is? Maybe point me in the right direction?
> Thanks again
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Thanks Carl

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!