By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,276 Members | 1,227 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,276 IT Pros & Developers. It's quick & easy.

Exporting SQL to Excel Hourly

P: n/a
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!
Jul 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a

"Anthony" <an*****@alfy.com> wrote in message
news:8c**************************@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
Jul 20 '05 #2

P: n/a


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!
Jul 20 '05 #3

P: n/a
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!
Jul 20 '05 #4

P: n/a
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" <an*****@alfy.com> wrote in message
news:40**********************@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!

Jul 20 '05 #5

P: n/a

Thanks Carl
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.