October 6, 2005 at 11:56 am
Ok This is really annoying.. I have a dts package that writes to an excel file and mails to a set of recipients when I run the package it executes just fine. However, when I schedule a run it fails. Initially I assumed that the file cannot be accessed, so I created the file on the server itself. Can anyone tell me what is missing I would greatly appreciate it..
The job failed. The Job was invoked by Schedule 64 (JobName). The last step to run was step 1 (JobName).
October 6, 2005 at 12:14 pm
It is probably a permissions issue.
In the sql server agent, who is the job set to run as?
if its sa, then you'll have issues because sa does not have rights to create a file.
October 6, 2005 at 12:48 pm
It's not a permission issue because I am running this under admin account
October 6, 2005 at 12:53 pm
>>It's not a permission issue because I am running this under admin account
You missed the point of the 1st response.
You state "*I* am running this under admin account". *You* and *your* permission set are irrelevant, when it's being run by SQL Agent.
October 6, 2005 at 1:10 pm
Kindly improvise on suggestion that will resolve the issue. I prefer simplicity.. Thanks..
October 6, 2005 at 1:21 pm
Open the DTS Package.
Right-click and select 'Package Properties'
Click on the 'Logging' tab.
In the 'Error Handling' section, enter a log filename for capturing the package errors.
Now schedule it again and provide us some meaningful error log data that gives us a bit more to work from than 'The job failed".
October 6, 2005 at 2:26 pm
That was much better Here is what I found under the package logs..
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Logon failed: MapiLogonEx Failed due to MAPI error 273: MAPI Logon failed.
Step Error code: 80040480
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:9100
October 7, 2005 at 10:21 am
When you run the package manually, it runs as the account you logged on as but when you schedule it, it runs as the account that sql server agent is configured to run as. Looks like these have different sets of permissions(specially to connect to mail server) in your case.
HTH
October 7, 2005 at 11:00 pm
Hi,
U say that when u run the DTS manually it runs fine and when u schedule it it gives error.
If u loged in sql server with ur admin account mapped with windows authenticatrion and u have enough permission then it will not show any error but as far as the error is conserned with mail profile it will not even run when u execute the DTS manually. it will show u the same error as in the error log.
Just drop ur mail profile and recreate the mail profile again.
from
Killer
October 8, 2005 at 5:00 am
It is a MAPI error not a file access error.
Just create a MAPI profile for the SQL Server Agent Account. To do that, open a Windows session using the Account used by the SQL Agent Service and create a MAPI account, mailbox, etc.
HABIB.
PS. After you finish, it is recommended that you take this account out of the list of users able to start an interactive session (local policy)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply