Multiple Emails With One DTS package

Question

Multiple Emails With One DTS package

ninel

Ten Centuries

Points: 1027
More actions
August 22, 2006 at 9:25 am

#67633

I have a web application that prompts a user for some info and creates databases on the fly with VB.NET.
Multiple databases can be created in one day. I need to create a DTS package that will be scheduled to run daily. This dts gathers some info on each database that got created. For example, 5 databases were created.
The DTS creates an email with some statistics in the body of the email and a csv file attachment of some info from the databases. At this point I haven't decided (or actually wasn't told by my manager) if the package should create:
1. separate emails (in this cases 5 emails) with the csv file attached to each email and stats in the body OR
2. one email with 5 attachments (for each db) and stats of all 5 dbs in the body OR
3. one email with one attachment, but 5 worksheets and stats of all 5 dbs in the body.
Are all of these scenaries possible within a DTS package. Remember, I won't know how many databases were created. I'd have to figure that out dynamically.
Thanks,
Ninel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 1

We have been using a simple e-mail program called "Blat" that uses SMTP. You can create scripts via the dts package to select the data into a file for the body of the e-mail, a script to send to one or multiple recipients and the csv file. The command syntax is simple:

C:\Email\blat C:\Email\EmailBody.txt -subject "Your Subject Line" -to email@email.com -attach "C:\Email\YourFile.csv"

If you are running SMTP services on your server already you can use sql mail or execute sp_send_cdosysmail or use the following SQL:

DECLARE @CDo int, @OLEResult int, @Out int

EXECUTE @OLEResult = sp_OACreate 'CDONTS.NewMail', @CDo OUT

IF @OLEResult <> 0 PRINT 'CDONTS.NewMail'

EXECUTE @OLEResult = sp_OAMethod @CDo,

'Send',

Null,

'From@email.com',

'to@mail.com',

'e-mail test...',

'CDO database e-mail test.',

1

IF @OLEResult <> 0 PRINT 'error: ' + CONVERT(VARCHAR(64),@OLEResult)

EXECUTE @OLEResult = sp_OADestroy @CDo

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 2

Hi, you might also like to try the sendmail stored procedure, xp_sendmail, details in BOL.

All the best,

Duncan

John McC Hall of Fame Points: 3410 More actions · Answer 3

Or, if you can't get that sorted out (I can never get it to work on our system)

http://sqldev.net/xp/xpsmtp.htm

Cheers

John

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy: