December 4, 2003 at 9:40 am
Does anyone experience the following, the job running is under my account, I am the local Admin and sysadmin on SQL server.
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: Copy Data from PSDivUnits to [eBilling].[dbo].[PSDivUnits] Step DTSRun OnError: Copy Data from PSDivUnits to [eBilling].[dbo].[PSDivUnits] Step, 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: Copy Data from PSDivUnits to [eBilling].[dbo].[PSDivUnits] Step DTSRun: Package execution complete. Process Exit Code 1. The step failed.
December 4, 2003 at 9:48 am
Check that the account that runs the SQL agent service has access to that file.
December 4, 2003 at 1:36 pm
Yes, everyone has full control on this file.
December 5, 2003 at 2:15 am
Questions....
Does the file sit on the same server as the one you have local admin rights to? Or is it on another server or even in another domain?
Is the file accessed by any other processes?
What are the permissions given to the proxy account?
December 5, 2003 at 4:44 am
Are you using a Job Output File (seen in the Advanced tab on thejob step)? We had a problem where the proxy account could not open this file, even though it appeared to have all the right permissions. Eventually we found it worked when using a different directory, but we never tracked down the cause of the problem.
All information provided is a personal opinion that may not match reality.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 5, 2003 at 7:54 am
Yes, the file is on the local server. The jobs were created by the consultant, NT admin disabled his account after he left. I changed the owner of DTS packages and all jobs to my account. It still gave me the access denied message.
December 5, 2003 at 8:31 am
Duoble check your permission of every db object inside the DTS packages and jobs.
December 5, 2003 at 8:33 am
a customer of mine had a problem and I found in their DTS package they were using UNC naming to designate a file. When I changed this to a volume like d: it worked.
you could also try to delete and recreate the file just to be sure no hidden issues.
December 5, 2003 at 9:57 am
Under the SQL Agent properties/Connection tab, check to see what account is there. If it's a network account, try using the sa account. This solved a lot of our authentication isssues when running DTS packages from jobs. Also try running the job with no account logged onto the server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply