March 19, 2009 at 10:54 am
Can anyone provide me with a really simple example of a DTS package, that sends an automated email to a recipient with a email message that contains the relevant records details:
e.g. the dts email package would do something like this
To: [$eMailRecipient]
Message:
Dear [$FullName],
Please contact us as soon as possible as your account ( [$AccountNo] ) is set to expire on $ExpireyDate.
blah blah blah
This would have to be sent for each record in a record set
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 19, 2009 at 12:39 pm
What version of SQL Server are you running with? DTS was for SQL Server 2000, SQL Server 2005 replaced this with SSIS, but still supports use of DTS packages, but for writing from scratch on SQL 2005 you'd use SSIS.
March 19, 2009 at 1:05 pm
My first thought is to do it via SQL job (cursor/loop) with SQL Mail (or Database Mail in 2005) functions
Something like
FOR every record in TABLE WHERE conditions = ...
BEGIN
EXEC send_email
@To
@From
@Subject
@Message
END
March 24, 2009 at 8:04 am
Hi Jerry,
I need to do it using SQL2000, so it would be DTS. I didn't realise it would be so simple.
I had an idea that it would invovle some DTS vb scripting to generate the email and other logic for reading each record.
Thanks for posting a reply back. I'll give it a go.
Thanks.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 25, 2009 at 6:22 am
1 other quick question - is send_email a stored procedure that I have to install / create or can I just call send_email.
Thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 25, 2009 at 6:27 am
is send_email a predefined SQL2000 stored procedure?
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 26, 2009 at 3:35 am
a bit familiar situation for me. You should create ActiveX Script task like this:
sub MessageToEmail(email, msg_textbody, msg_subject)
if email <> "" then
on error resume next
const cdoSendUsingPort = 2
smtp_srv = "xxx.xxx.xxx.xxx"'your SMTP server IP
msg_from = "admin@yourdomain.com" ' sender address
dim cfg, fld
set cfg = CreateObject("CDO.Configuration")
set fld = cfg.Fields
fld("http://schemas.microsoft.com/cdo/configuration/smtpserver")= smtp_srv
fld("http://schemas.microsoft.com/cdo/configuration/smtpserverport")= 25
fld("http://schemas.microsoft.com/cdo/configuration/sendusing")= cdoSendUsingPort
fld.Update
dim msg
set msg = CreateObject("CDO.Message")
set msg.Configuration = cfg
msg.To = email
msg.From = msg_from
'msg.BCC = "admin@yourdomain.com" 'you can add your email here perhaps..
msg.Subject = msg_subject
msg.HTMLBody= msg_textbody
'msg.AddAttachment fName 'attachment path, if you need to add some file
msg.Send ' send the message
set msg = nothing
set fld = nothing
set cfg = nothing
end if
end sub 'MessageToEmail
Function Main()
DSN = "Provider=SQLOLEDB.1;Password=[xxxxxxx];Persist Security Info=True;User ID=[xxxxxxx];Initial Catalog=[DatabaseName];Data Source=(local)"
set rs = CreateObject("ADODB.Recordset")
rs.Open "select UserName, UserEmailAddress, Expire_Date from UserTable where Expire_Date>=getdate() and Expire_date<=Dateadd("d", 7, getdate()) ", DSN
if not rs.eof then
do while not rs.eof
UserName= rs.Fields("UserName")
UserEmailAddress= rs.Fields("UserEmailAddress")
Expire_Date= rs.Fields("Expire_Date")
MessageToEmail UserEmailAddress, "your message text", "Your account is expiring"
rs.MoveNext
loop
end if
rs.Close
set rs = nothing
Main = DTSTaskExecResult_Success
End Function
March 30, 2009 at 4:43 am
Hi,
thanks for the post - I'm giving it a try but i'm getting an error:
Error Source: Microsoft Data Transformation Services (DTS) Package
Error Description: Error Code: 0
Error Source= Microsoft VBScript compilation error
Error Description: Expected end of statement
Error on Line 44
--> Line 44 is the select statement line by the way - but I suspect it's more to do with the DSN connection. Below is the rest of the code.
Function Main()
DSN = "Provider=SQLOLEDB.1;Password=[sqlemail];Persist Security Info=True;User ID=[sqlemail];Initial Catalog=[connxodbc];Data Source=(local)"
set rs = CreateObject("ADODB.Recordset")
rs.Open "select name, email, expiry_date from contacts where expiry_date >= getdate() and expiry_date <= Dateadd("d", 7, getdate() ) ", DSN
if not rs.eof then
do while not rs.eof
UserName= rs.Fields("name")
UserEmailAddress= rs.Fields("email")
Expire_Date= rs.Fields("Expiry_Date")
MessageToEmail UserEmailAddress, "your message text", "Your account is expiring"
rs.MoveNext
loop
end if
rs.Close
set rs = nothing
Main = DTSTaskExecResult_Success
End Function
is there anyway I can test the DNS connection by it's self (sorry to be a pain but VB isn't something I do very often
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
March 30, 2009 at 5:07 am
andrius
thanks - I've now got it working (I had to remove the square brackets and also the " " in the dateadd statement.
Thanks a lot, this will be really useful.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply