how can i create a dts package to do the following:
1) execute a query once a day at a specified time,
2) either put the query results into the body of an email or output to a
file and attach that file to an email message
3) email the file

tia
jt

Some options-

xp_sendmail - supports mail and use @query parameter to pass in your query.

xp_smtp_sendmail (http://www.sqldev.net) - more reliable mail, but no @query
support. I use sp_makewebtask to create a HTML file which I use as the body
of the email, as xp_smtp_sendmail supports this. Can get some nice formatted
mails with tables in like this.

Use DTS to create a file of query output, and use either of the above or the
DTS Send Mail task to send the mail.

Use SQL Server Agent to schedule any of the above options.

Use SQL Reporting Services. It has formatted query output aka a report,
schedules and email capabilities.


--
Darren Green
http://www.sqldts.com


"JT" <jt (AT) nospam (DOT) com> wrote

Another option is to use Gadami. See http://www.kripsoft.com. It
supports sending queries as HTML tables in emails. Data can also be
attached as Excel spreadsheets. While you can tell Gadami to run the
query no more than once a day, you will need to use a scheduler to
actually run the program, like Windows Task Scheduler or SQL Agent.

-Krip