December 11, 2006 at 7:22 am
I have SQL 2000 DTS packages that I am trying to set up as Jobs on SQL 2005. I can run command line and execute DTS package, when I use same command line in a Job as Step it tells me:
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Error opening datafile: Access is denied.
dtsrun /S "(local)" /E /N "PB_Active_InspectorReportExport"
December 12, 2006 at 12:59 am
You have to make sure that the service user of the sql agent has permissions to run DTSrun (access to the file) and that it can connect to the local sql server and run the package.
December 12, 2006 at 6:56 am
Add a fully qualified path to DTSRUN.exe
"C:\PROGRAM FILES\MICROSOFT SQL SERVER\80\TOOLS\BINN\DTSRun.exe" /S "(local)" /E /N "PB_Active_InspectorReportExport"
December 12, 2006 at 7:52 am
I've added the full path, checked permissions on Db,files and all seem correct.
I'm posting error, hoping it may provide more clues.
Message
Executed as user: IIS-B\SQL. 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.
December 12, 2006 at 8:34 am
When dts packages are run as jobs, the security context that the packages run under usually changes. When you run the package manually you are using your login id and the associated security context. When you set up a job, on the step dialog you specify the security context the step should use. I'm guessing that for most, it is the SQL Server Agent Service and its security context. Typicaly you can see what username is used to run the Agent Service in your case IIS-B\SQL. Like any process accessing data from a file, in a directory and possibly using a share, permission needs to be granted the user id accessing the resource. In this case make sure that IIS-B\SQL has read / write permission to the flat file being accessed in the package and also that IIS-B\SQL can get to the file via the root of the path down to the folder conating the file. If you're using shares then you would also have to grant permission to the share to IIS-B\SQL.
December 12, 2006 at 10:18 am
Resolved: Thanks for the help!
I set permissions to the file being written to everyone and that appears to have solved the problem
Thanks to all who responded... RM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply