Change Email Task Properties

HI,

How can I programmatically change the properties of Send Mail task using Script Task. I want to change the From, To and Attachment parameters of the Send Mail task.

If the Script Task can't do it, Is there any alternative to do?

Thanks

All those questions are answered here: http://blogs.conchango.com/jamiethomson/archive/2006/06/28/4156.aspx

-Jamie

CDOSYS Mail Using SQL Server

The procedure works without problems for the basic 'textbody' and 'htmlbody' type messages. The problem is I would like to get the CreateMHTMLBody method working. This works beautifully as a VBScript which I'm attempting to convert to SQL.

CREATE PROCEDURE usp_send_cdosysmail
@.From_Addr VARCHAR(500) ,
@.To_Addr VARCHAR(500) ,
@.Subject VARCHAR(500),
@.Body VARCHAR(4000) ,
@.SMTPserver VARCHAR(25) = 'localhost',
@.BodyType VARCHAR(10) = 'textbody'
AS

DECLARE @.imsg INT
DECLARE @.hr INT
DECLARE @.source VARCHAR(255)
DECLARE @.description VARCHAR(500)
DECLARE @.output VARCHAR(1000)

EXEC @.hr = sp_oacreate 'cdo.message', @.imsg out
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").value','2'
EXEC @.hr = sp_oasetproperty @.imsg, 'configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").value', @.SMTPserver
EXEC @.hr = sp_oamethod @.imsg, 'configuration.fields.UPDATE', NULL

EXEC @.hr = sp_oasetproperty @.imsg, 'to', @.To_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'from', @.From_Addr
EXEC @.hr = sp_oasetproperty @.imsg, 'subject', @.Subject

IF @.BodyType <> 'URL'
BEGIN
EXEC @.hr = sp_oasetproperty @.imsg, @.BodyType, @.Body
END
ELSE
BEGIN
-- Generate The Body From The Input URL
EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''
END

EXEC @.hr = sp_oamethod @.imsg, 'send', NULL

When I test the "EXEC @.hr = sp_oamethod @.imsg, 'CreateMHTMLBody', @.body, 0, '', ''" step it gives me a blank email.
The return code (@.hr) for that step is -2147211483 (x80042725).
There is no error message. Search as I might, I haven't been able to find a reference to that error.I found the answer:
EXEC @.hr = sp_OAMethod @.imsg, 'CreateMHTMLBody', NULL, @.body, 0, '',''
works!!!!

cdonts send mail T-SQL - IIS SMTP setup

hello,

I would like to send mail from sql server using CDONTS.

I have the sql code for this and it works for internal addresses.

I get an error/warning saying that it cannot relay for external addresses when I try an outside domain.

Is there settings in IIS for the SMTP service that I can change to allow it to relay to the external domains?

I have everything running on the SQL Server, and I do not have access to the exchange server.

ThanksCDONTS sends email to the Local SMTP server. You can specify a "smart host" that the local server can forward any email it can't deliver.

Search the help for the IIS SMTP Virtual Server. It's likely the network blocks any unapproved system from using the smtp port, and it's also likely the approved ones will need to allow your system to relay through them.

Jay Grubb
Technical Consultant
OpenLink Software
Web: http://www.openlinksw.com:
Product Weblogs:
Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
UDA: http://www.openlinksw.com/weblogs/uda
Universal Data Access & Virtual Database Technology Providers

CDONS.mail not working

I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>

CDO mail attachment is not working

Hi
I have some SQL code below which I'm running on a server DEV_TESTSTAGE2 with
Windows 2000 and SQL Server 7. I'm trying to send an attachment which works
fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
somewhere else on the network it send the email without the attachment and I
get the error message below. I've had a look on the internet but most of the
web peges refer to permissions problems in ASP. In my case I'm using SQL
Server objects and I'm running the query with Windows authentication and my
NT logon id
has access to the file attachement . I've tried running the query using SQL
authentication but I get the same error.
Any help would be gratefully appreciated
-2147024891
Source: CDO.Message.1
Description: Access is denied
declare @.HResult int
declare @.HR int
declare @.iMsg int
declare @.Text varchar(8000)
Declare @.source varchar(255)
Declare @.output varchar(1000)
Declare @.description varchar(500)
declare @.attachfile varchar(1000)
--************* Create the CDO.Message Object ************************
exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
--error handling for failure to create object......
select @.HResult, @.iMsg
--***************Configuring the Message Object ******************
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendus
ing").Value','2'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rver").Value','CHOPGBBES001.gb.cannonsgroup.net'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpse
rverport").Value','25'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpus
essl").Value','False'
exec @.HResult = sp_OASetProperty @.iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpco
nnectiontimeout").Value','60'
-- Save the configurations to the message object.
EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
-- Set the e-mail parameters.
EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
set @.attachfile = '\\flavius\IT\pstat.txt'
--set @.attachfile = 'c:\boot.ini'
EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
--IF @.hr = 0
-- BEGIN
SELECT @.output = ' Source: ' + @.source
PRINT @.output
SELECT @.output = ' Description: ' + @.description
PRINT @.output
RonanRonan
Do you have any permissions issue to the folder/file?
in addition ,take a look at
http://www.sqldev.net/xp/xpsmtp.htm
"Ronan" <Ronan@.discussions.microsoft.com> wrote in message
news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
> Hi
> I have some SQL code below which I'm running on a server DEV_TESTSTAGE2
> with
> Windows 2000 and SQL Server 7. I'm trying to send an attachment which
> works
> fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is
> somewhere else on the network it send the email without the attachment and
> I
> get the error message below. I've had a look on the internet but most of
> the
> web peges refer to permissions problems in ASP. In my case I'm using SQL
> Server objects and I'm running the query with Windows authentication and
> my
> NT logon id
> has access to the file attachement . I've tried running the query using
> SQL
> authentication but I get the same error.
> Any help would be gratefully appreciated
> -2147024891
> Source: CDO.Message.1
> Description: Access is denied
>
> declare @.HResult int
> declare @.HR int
> declare @.iMsg int
> declare @.Text varchar(8000)
> Declare @.source varchar(255)
> Declare @.output varchar(1000)
> Declare @.description varchar(500)
> declare @.attachfile varchar(1000)
>
> --************* Create the CDO.Message Object ************************
> exec @.HResult = sp_OACreate 'CDO.Message', @.iMsg OUT
>
> --error handling for failure to create object......
> select @.HResult, @.iMsg
>
> --***************Configuring the Message Object ******************
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/send
using").Value','2'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
server").Value','CHOPGBBES001.gb.cannonsgroup.net'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
serverport").Value','25'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
usessl").Value','False'
> exec @.HResult = sp_OASetProperty @.iMsg,
> 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtp
connectiontimeout").Value','60'
>
> -- Save the configurations to the message object.
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Configuration.Fields.Update', null
>
> -- Set the e-mail parameters.
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'To', 'myemailaddress'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'From', 'TESTSend'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'Subject', 'this is the subject'
> EXEC @.HResult = sp_OASetProperty @.iMsg, 'TextBody', 'this is the body'
> set @.attachfile = '\\flavius\IT\pstat.txt'
> --set @.attachfile = 'c:\boot.ini'
> EXEC @.HResult = sp_OAMethod @.iMsg, 'AddAttachment', Null,@.AttachFile
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
> EXEC @.HResult = sp_OAMethod @.iMsg, 'Send', NULL
>
> EXEC @.hr = sp_OAGetErrorInfo @.HResult, @.source OUT, @.description OUT
> --IF @.hr = 0
> -- BEGIN
> SELECT @.output = ' Source: ' + @.source
> PRINT @.output
> SELECT @.output = ' Description: ' + @.description
> PRINT @.output
>
>
> --
> Ronan|||Hi Uri
Yes I should have mentioned I do have access to the file...it has 'Everyone'
permission
I had a look at the article you sent me...it refers to xp_smtp_sendmail...I
can't find that extended stored procedure in the master database of SQL
Server 2000...I have xp_sendmail but that requires you set up
a MAPI client which I don't want to do I want to send it via SMTP
Ronan
"Uri Dimant" wrote:

> Ronan
> Do you have any permissions issue to the folder/file?
> in addition ,take a look at
> http://www.sqldev.net/xp/xpsmtp.htm
>
>
> "Ronan" <Ronan@.discussions.microsoft.com> wrote in message
> news:767F2973-2782-4574-9940-CA949D740C1F@.microsoft.com...
>
>|||I have used the xp_smtpsendmail extended stored procedure. It works well,
but you will have to download the .ddl ..google it, and you'll find it. I've
used it to send reports generated as text files to business managers in the
past with great success...you will have to copy it to your sql\bin folder,
and registewr it inside sql, to get it to work...easy to do.
"Ronan" wrote:
> Hi Uri
> Yes I should have mentioned I do have access to the file...it has 'Everyon
e'
> permission
> I had a look at the article you sent me...it refers to xp_smtp_sendmail...
I
> can't find that extended stored procedure in the master database of SQL
> Server 2000...I have xp_sendmail but that requires you set up
> a MAPI client which I don't want to do I want to send it via SMTP
> --
> Ronan
>
> "Uri Dimant" wrote:
>|||Hi Tom
I downloaded and installed xp_smtp_sendmail and again it works fine for
attachements
which are on the machine where xp_smtp_sendmail is registered but it
still doesn't seem
to work when the attachment is on a different machine. I think I'll just
have to put my attachments
on the machine where xp_smtp_sendmail is registered as a work around.
Thanks for your help.
Regards
Ronan
Ronan
"Tom Mongold" wrote:
> I have used the xp_smtpsendmail extended stored procedure. It works well,
> but you will have to download the .ddl ..google it, and you'll find it. I'
ve
> used it to send reports generated as text files to business managers in th
e
> past with great success...you will have to copy it to your sql\bin folder
,
> and registewr it inside sql, to get it to work...easy to do.
> "Ronan" wrote:
>

Cast @[System::StartTime] Problem

This is a Subject Expression I put in my Mail component in my SSIS 2005 package but it's not liking the StartTime concatenation. I tried other ways but can't get it to allow this:

"Process Started by : " + @.[System::UserName] + (DT_DATE) @.[System::StartTime]

I get the error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""Process Started by : " + @.[System::UserName] + (DT_DATE)@.[System::StartTime]" failed with error code 0xC0047080.

I tried to use CAST() but not sure if that's allowed or the syntax for it specifically in SSIS 2005

As the error message says, you cannot concatenate DT_WSTR & DT_DATE. Try:

"Process Started by : " + @.[System::UserName] + (DT_WSTR) @.[System::StartTime]

-Jamie