Hi, I have a DTS package to export data into a text file. It works fine when i execute the DTS package itself, and there is a text file generated. However, when i scheduled it to run in Job, the job failed to execute the DTS by returning this error message: 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. What's going on with it?
It looks like a security issue. Check the account which SQLAgent runs under. You will probably find that this particular user does not have access to the folder it needs to save the file to.