Batch File in DTS.

[namtab]

I have a DTS package which executes a .bat If I execute the package it works fine but when I schedule it, it fails.

All im doing in the .bat is renaming a text file and I have change directory to point to the correct location of the text file.

Any ideas?

 

[JayKusch]

When the job fails, does it just not run or does it attempt to run and then fail? What does the job history tell you. Make sure the job write to the event log and check the event log, as well as the sql server error logs, for any hint as to why it is failing.

I am betting it is a permissions issue on the share/folder you are trying to write to.

Also, is the share that you are performing the file manipulations on the same server as SQL Server or is it a remote share on another server?

Thanks

J. Kusch

 

[namtab]

the event log gives the following message:-

************************************************************
The system cannot find the file specified.
DTSRun: Loading...

DTSRun: Executing...

DTSRun OnStart: DTSStep_DTSCreateProcessTask_1

DTSRun OnError: DTSStep_DTSCreateProcessTask_1, Error = -2147220330 (80040496)

Error string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 1, which does not match the specified SuccessReturnCode of 0.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 4900



Error Detail Records:



Error: -2147220330 (80040496); Provider Error: 0 (0)

Error string: CreateProcessTask 'DTSTask_DTSCreateProcessTask_1': Process returned code 1, which does not match the specified SuccessReturnCode of 0.

Error source: Microsoft Data Transformation Services (DTS) Package

Help file: sqldts80.hlp

Help context: 4900



DTSRun OnFinish: DTSStep_DTSCreateProcessTask_1

DTSRun: Package execution complete.
**************************************************

The share is on the same server ie D:\
as the error message says it can't file the specified file yet I have put the full path in. and they work because itexecutes it just doesn't work when it gets scheduled

 

[JayKusch]

Make sure that the account you are running SQL Server has rights to that share also.

Thanks

J. Kusch

 

[EdwinGene]

I've had this same problem, namtab. Like JayKusch says it was a permissions problem. When I ran the DTS package from the Designer it was running under the context of my own network user login, but when I scheduled the package it was running under the context of the SQL Server network user login. The SQL Server network user login needs to have correct permissions on all directories and shares the DTS package is trying to access.

 

[namtab]

Im having a look at my permission at the moment. The only thing is I have set the agent to run under my account so I would imagine it has my security. I have loggen on the Server as myself so when I execute the package it works with my permissions and sqlagent has my Service Startup account yet still fails.

 

[namtab]

I solved it. it was my .bat was wrong.
Thansk for all the help guys at least I learnt about permissions.