Hi,

I have a DTS job with a send mail task that required a change to the
attached file parameter.
As the task isn't generic, we simply opened the send mail object and
changed the relevant value. Once we resaved the package, MAPI logins
failed.

I checked the task, and it turns out that it is using the incorrect
profile. The valid SQL Mail profile is not available for selection, so
this can't be corrected.
For security reasons, developers are unable to log onto to the server
using the account that runs SQLagent, so we can't just log in to the
correct profile.
We can view / test the SQL Mail profile via Enterprise Manager, but it
doesn't appear in the Send Mail task.

As a work around, I used a dynamic properties to set the profile and
now everything is fine.

My question is - is there a better way of doing this? I'd rather not
have to set dynamic properties for every send mail task that runs. We
are setting up several off-line report generators, and would like the
users to receive emails when their report is complete. This means that
each package will contain multiple Send Mails, and dynamically setting
each one is ridiculous...

Specs are :
SQL 2000 sp3
WinNT 4.0 sp6
Exchange 5.0.

TIA,

Adam

In message <a6c4b990.0402041558.1d89a3ce (AT) posting (DOT) google.com>, Adam
<notgotmuchspaminit (AT) yahoo (DOT) com.au> writes
Adam, the Send Mail task is pants because it uses MAPI. One of the
problems with MAPI is of course that the profile names must match. Those
profile names are local the logged on user, and isnce Dts is client-side
that means each dev who builds a package probably uses his own profile
name. Not pretty.

Personally I do not use the task because of this. I use an Exec SQL Task
with xp_smtp_sendmail, via my own wrapper proc. (http;//www.sqldev.net).

You could avoid setting Dynamic Properties on each task by adding a
standard ActiveX Script Task to each package. Consider it like a
template, which should be part if every good design already for the
normal properties that change, e.g. connections, paths etc.

In script you can enumerate all tasks, looking for send mail tasks, and
automatically set the profile to a defined value. I suggest that this
value is stored in a global variable. I always set certain global
variables on load, or as part of the first task.

DTSRUN and /A is good for setting on load, and the Dynamic Properties
Task, either with SQL query or INI files as a standard config lookup
mechanism also work well.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thanks Darren,

should have thought of this myself. I already do this for several
connection objects, but for some reason didn't think of it for the
send mail task...

Adam