DTS Mail question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS Mail question

Author

Topic

etietje
Starting Member

24 Posts

Posted - 2003-02-04 : 09:26:21

I am extremely new to DTS. Let's start there :)

What I need to do is send a scheduled email to a customer that includes a PCT of records that are in one table vs another, and the list of records from one of the tables. I would like to know if there is a way to dynamically build the subject area of an email task in DTS to include the percentage in a sentence, then attaching an Excel spreadsheet with the records. Is this possible, and if so, how exactly can I do it?

Thank you in advance.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-04 : 11:24:38

Well instead of using the mail object in DTS, you could instead use the SQL object and execute xp_sendmail instead. xp_sendmail can do what you need it to do dynamically.

etietje
Starting Member

24 Posts

Posted - 2003-02-04 : 11:27:21

That's great, but what is xp_sendmail? Like I said, I'm pretty new to this.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-02-04 : 16:31:42

Seek and ye shall find

http://www.sqlteam.com/SearchResults.asp?SearchTerms=mail

Damian

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-02-04 : 19:22:23

Yes,

You can use an ActiveX Task to access the DTS object.

Here is an example of modifying a file path in an bulk insert task.


'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************

Function Main()
Dim objPackage
Dim strFilePath

strFilePath = DTSGlobalVariables("RevFilePath").Value & "REV"
strFilePath = strFilePath & ".csv"

Set objPackage = DTSGlobalVariables.Parent
objPackage.Tasks("DTSTask_DTSBulkInsertTask_1").CustomTask.DataFile =  strFilePath

Main = DTSTaskExecResult_Success
End Function

To learn more about the object model you can save the DTS task as a vb module .bas and take a look at the code with a text editor.

xp_sendmail is an extended stored procedure meaning its an interface to a dll which uses OS mailing librariess CDONTS/SMTP for sending email.

Another method of doing this would be to use extended stored procedures to create a mailing object CDONTS and use the object directly. This method does not require a mapi profile and does not really on having the sql mailer agent enabled.

Edited by - ValterBorges on 02/04/2003 19:27:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources