July 23, 2002 at 9:00 am
hey guys,
i am getting an error while running a job using sql agent. What i am trying to do is, i login to SQL SERVER NT box with my login name with admin privileges. Then i create a package with a delimited file as my source and OLE DB SQL SERVER database table as my destination. The logon to this database is "SA". I have set it up to run each morning at 7am. However, the job fails each day. But if i go in and manually execute this job, it works well. Following is the error message that i get. Let me know if you can help me out on this.
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217887 (80040E21) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
July 23, 2002 at 9:26 am
To figure out exactly what could be the cause of the failure set a log file. When you run the package this log will pin point exatly to the error.
To set the log open the package, go to "Package" and select "Properties."
There, select the "Logging" tab and enter the path in the "Error file" box for the text file the package will create as soon as it finishes.
With this log you will be able to fix the problem. The log history you are reading is from the job history? It usually print a message to let you know it failed, but it does not pin point to the problem you are having. I hope this small contribution helps a little. Good Luck
July 23, 2002 at 9:28 am
Sorry, I forgot. It looks like it is a permission issue. Are you set your authentication for SQL and NT?
July 23, 2002 at 10:04 am
Excuse me? i don't get it. I have admin privileges in the NT box and i use "SA" as the login for the SQL server in the package that i run. Should i change the login? please advise
July 24, 2002 at 7:49 pm
This issue sounds very similar to what happened to me. It too sounds like a permission problem.
You're able to run the DTS package ok under your login, but not under the scheduler. The reason for this is that the scheduler runs under its own account (right-click SQL Agent and go to Properties).
More often than not, this is usually a system account (default), which can have troubles due to a)insufficient permissions to access network paths etc and b)user variables not defined as global & c) a whole bunch of other stuff.
Simple test. Stop the SQL Server Agent, change the account to your account details, and restart the Agent again. Run a Scheduled test - if it works, it's due to login permissions for the agent.
The fact that within the DTS package you're logging in as 'sa' means nothing when everything depends on the user kicking it off.
Hope this helps.
July 25, 2002 at 7:29 am
Brendon is right. The Issue your Scheduled job is failing because the SQL server agent does not have enough Privileges on the cal Box where SQL server is Installed. .Pls Check which account is used to Start the SQL server Agent. If you are using a System account then it is local to the system from which you tried to Start the job . Always use a domain account with necessary admin privileges on the local box where SQL server is installed to start the SQL agent.
July 25, 2002 at 9:21 am
Thanks Brandon & Elias. I was able to fix it the same way you guys had mentioned in your messages. It works fine now. Thanks very much.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply