August 20, 2008 at 7:43 am
Hello
I have created a DTS and this works fine when it is run. But when this is scheduled as a job, it doesnt work and gives an error. The job runs less than a few seconds and fails.
I have several other DTS which are scheduled as jobs and have no problems, but this one isnt working. I tried using DTS run and when this failed, tried as a TSQL command using exec master.dbo.xp_cmdshell ' ' , that failed too. I am using batch files & I used OSQL command in the batch files. The DTS is copying csv files to another server - could that be any issue? can someone please help me in this issue?
August 20, 2008 at 9:54 am
It would be easier to help if we knew the error you were getting. Several folks have looked at the post, but can't answer because we don't know the error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 10:19 am
Thanks for the reply. Here is the error message:
It shows Access is Denied, but nothing has changed recently and I have other DTS packages running without any problems.
Executed as user: BALTIMORE\service.SqlTest. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSCreateProcessTask_1 DTSRun OnError: DTSStep_DTSCreateProcessTask_1, Error = -2147024891 (80070005) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error Detail Records: Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 1100 Error: -2147024891 (80070005); Provider Error: 0 (0) Error string: Access is denied. Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 4900 DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
August 20, 2008 at 10:25 am
Looking at the "Access Denied" in the error statement I would say that the scheduled DTS is running as a different user when scheduled. That user does not have right to do what your trying to do, or rights to access the data you're trying to access.
Check which user the job runs as.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 20, 2008 at 11:07 am
Does that mean - Owner?
I just checked and see that my name is under the owner for DTS. I have several other DTS set up similar to this.
The DTS created files on a network share- could that be access denied on this server?
I checked with sys admin and was told that this account is an admin on the box.(server where the file is being created.
August 20, 2008 at 2:46 pm
The job runs under the account that started the SQL Server Agent service. Make sure that that account has adequate rights to run the DTS and has access to the appropriate folders.
_____________________________________________________________________
- Nate
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply