i have had to move a job from one SQl server onto another after a disc failure, It basically runs a few SQL scripts and outputs a .csv file on to a shared root drive.
When i execute the DTS package on its own i have no problem. However when i do so through a job it errors with the following message:
Executed as user: SUSSEX\*******. DTSRun: Loading... DTSRun: Executing... 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. Process Exit Code 1. The step failed.
I have set up a new job and a new DTS package from scratch (even through the servers own SQL enterprise manageer) and am using the full UNC path name. When i remote desktop into the server i can access the UNC path and can read and write to it no problem. I have been googling but can not find a solution thus far that has been helpful. This has happened in the past and a new job and DTS package seemed to fix it so i'm really puzzled now.
I have the same job(writing to an adjacent folder) on another server that is working fine and when i have added the E:\reports\pim_user.csv path (i.e writing to itself) the job has worked fine. Only when i change it to \\MRFT001\appfileoutput$\CIS PiMs USER Emails\spt_pims_users.csv does it then misbehave saying access denied....
Any help would be much appreciated
Please check the "Run As" property in job step and make sure that the user/proxy you are using to run job has appropriate access to the source directory. If that user does not have access to source directory, you have to create proxy account or give proper access to the existing account you are using to run job. The thing is, when you run your package using DTExecUI.exe, it will using your credential to look the file in source directory and that is why it is running from file system.
- Proposed as answer by Todd C Monday, August 23, 2010 2:18 PM
but this is SQL2000 (DTS packages) and i have also checked to make sure the account has been given correct access. When remote desktopped in to that account on the server i can read and write to the directory. In the View History the job has been executed by the same account so this does not appear to be the problem.