February 22, 2008 at 12:12 pm
I am trying to schedule a fairly simple DTS job. It drops a table, re-creates the table, picks up a text file from a file server and enters the data into a SQL 2000 table. I keep getting an error:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
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.
From Enterprise Manager I can run the package and it runs fine. So I verify the account that SQLserver agent runs under has rights to the file. It does. I physically logged onto the database server with account that runs SQLserver agent, sucessfully browsed to the file share and ran the DTS job. It ran successfully. Then I scheduled it to run on that server and it fails with the error above. So I can run the job as the SQL agent user, but I can't schedule it to run with the SQL agent user. Can anyone offer me any advice?
Thanks,
February 22, 2008 at 12:27 pm
Who is the owner of the job?
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 22, 2008 at 12:44 pm
The owner of the DTS Job or the owner of the scheduled task?
The owner of the DTS job is the user who created it. The owner of the Scheduled task is the same account that runs the SQL server agent, who is a SQL sysadmin
February 22, 2008 at 1:03 pm
Michael Deputy (2/22/2008)
The owner of the DTS Job or the owner of the scheduled task?The owner of the DTS job is the user who created it. The owner of the Scheduled task is the same account that runs the SQL server agent, who is a SQL sysadmin
Hello Michael,
I think it is the permission issue. Even though the DTS is scheduled as the SQL Server Agent, it is trying to run the package as the owner who might not be having any permission to access the file share. So it would be better if you can change the owner of the DTS package as to the Agent, then execute it and it will be successful.
Hope this helps.
Thanks
Lucky
February 22, 2008 at 1:20 pm
Tried that it didn't work. Here is our latest theory. The SQL Server agent was started before the account that runs the SQL server Agent was given access to that folder. Our theory is that the SQL Server agent doesn't know it has rights to the file share even though logging on the server proves the account has rights. This is getting confusing... let's call the user that runs SQL Server Agent domain\bob.
domain\bob owns the DTS package
domain\bob has read rights to the folder the data resides
domain\bob owns the scheduled job
domain\bob is the account that starts the SQL server agent
the SQL server agent was started several months ago
domain\bob was given rights to the folder this week.
is it possible that the SQL server agent running under domain\bob does not know he now has access to that folder or would SQL server agent check with AD at the time it tries to access the file?
February 25, 2008 at 5:30 am
Restarting the SQL agent has solved the problem.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply