Generic DTS Package for emailing excel -- sql server dts

blarfoc NO[at]SPAM yahoo.com
7/28/2005 8:54:02 AM

Hello, I have a need for a web application to request a stored
procedure to be run and then the results emailed to the user in Excel.
My web applicaiton is in ASP.C#.NET. But I want to use parameters to
supply the

stored procedure name
excel file name
email address

Should I use the DTS designer or should I have my web application build
the DTS package from scratch each time? I think i can't use the
designer window because I don't know the names of the stored procedure
or email address or excel file each time.

Please tell me how is the best way to approach? I don't want to create
a DTS package for each stored procedure because the procedures and
parameters can be different every time.

Douglas Laudenschlager [MS]
7/28/2005 10:50:36 AM

While I'm a big fan of DTS, you may be able to eliminate some complexity by
(1) exporting your data to Excel directly from your stored procedure(s) by
using a SELECT INTO statement, for example, then (2) simply emailing the
file using SMTP from your web app.

Here's one article where you can find an example of the SELECT INTO syntax,
although the context is not a stored procedure:

Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295646

-Doug
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA

This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

sql server dts : Generic DTS Package for emailing excel