MAPI LogonEx failure problem

[sfriedman451]

I have created a package which uses the datapump to create an excel file from the a table and then sends this by email to clients as an attatchment.

I have run this two days in a row now in different forms

Run directly from package utilizing execute command
Succeeds.

Run from command line using dtsrun /SARASQLSERVER1 /E /N”TestSF1”
Succeeds

Run as a Job from SQLAgent
Fails 100% of the time but does send failure notification.

SQL Agent Job Parameters
Target local server
Check enabled
Owner ARASC\SQLServices

Steps – Operating System Command - “Run Job” – dtsrun /SARASQLSERVER1 /E /N”TestSF1” – Output file c:\Temp\sf_output_test_mail.txt
Notifications –email operator sfriedman (when job fails)
Result
JOB RUN: 'Test_For_Mail' was run on 5/23/02 at 5:22:12 PM
DURATION: 0 hours, 1 minutes, 29 seconds
STATUS: Failed
MESSAGES: The job failed. The Job was invoked by User sqladmin1.
The last step to run was step 1 (Run job).

Error log
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSDataPumpTask_1

DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 686 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 686

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1

DTSRun OnStart: DTSStep_DTSSendMailTask_1

DTSRun OnError: DTSStep_DTSSendMailTask_1, Error = -2147220352 (80040480)

Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 1100



Error Detail Records:



Error: -2147220352 (80040480); Provider Error: 0 (0)

Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 1100



DTSRun OnFinish: DTSStep_DTSSendMailTask_1

DTSRun OnStart: DTSStep_DTSSendMailTask_3

DTSRun OnError: DTSStep_DTSSendMailTask_3, Error = -2147220352 (80040480)

Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 1100



Error Detail Records:



Error: -2147220352 (80040480); Provider Error: 0 (0)

Error string: Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts.hlp

Help context: 1100



DTSRun OnFinish: DTSStep_DTSSendMailTask_3

DTSRun: Package execution complete.


From everything I can gather the problem is not with the package but something with the SQL Jobs interacting with the MAPI server. Exactly what is a mystery to me since the same dtsrun command seems to work from the command line. Can anyone give me aclue as to what to fix here?

 

[tlbroadbent]

When you run a package from Enterprise Manager or the command line the security context of the package is that of the Windows account you used to log in to that computer. When the package is run as a scheduled job, the package is always run on the server using the SQL Agent account credentials.

A frequent reason that jobs fail is inadequate permissions for the SQL Agent account. In this case, the account is not able to access the profile or the mail server.

See the following article for details about scheduling a pacakage as a job.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q269074

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.