Scheduled DTS jobs no longer running

[Sweenster]

We have scheduled DTS jobs that have run error free for ages but the ones that use batch files to build cubes in Transformer have stopped running when scheduled.

It is not specific to this program as I am unable to schedule a DTS package to run *any* .exe [or a .bat which launches an app]. An instance of that programs appears in task manager but the program window does not appear and the job just keeps running.

Job is owned and run by member of sysadmin.

Any ideas??

 

[DrSql]

Refer to this.. May be it might give you some trouble shooting idead.

http://www.tek-tips.com/viewthread.cfm?qid=1119590&page=1

Dr.Sql
Good Luck.

 

[JBaileys]

Are you able to run the batch jobs manually, (unscheduled). We had a couple issues. One being a domains permissions were not authenticating properly. UNC path crapped out. Can you try to isolate your job to a single machine and see if it works? Or is that not reasonable?

 

[Sweenster]

Yes running manually via right click 'execute package' works fine as does using the windows scheduler to run a batch file that then runs the DTS package. It is only a problem when run via the scheduler in SQL server.

The jobs in question all run on the local machine with all data sources being on that machine's local drives.

 

[DrSql]

is this a security issue? The user thats running your package has ADMIN previlage to the box. What are trying to accomplish through the batch file. In your DTS package what TASK using to execute your batch file. Use "Execute Process Task"

Here is an example of DOS commands that I use to copy files.

Description: Copy Backups
Win32process: C:\Windows\SYstem32\xcopy.exe
Parameters: /Y \\SERVERNAME_Source\D$\mssql\backup\ \\SERVERNAME_DESTINATION\d$\MSSQL\BACKUP\
Return Code: 0
Timeout: 0

Dr.Sql
Good Luck.

 

[Sweenster]

The packages use an 'Execute Process Task' to run a batch file which launches Cognos Transformer and then build a cube.

It's the same user logged into Windows, MS SQL Server & SQL Server Agent. This user also have full access to all local drives.

The really annoying thing is that these have been scheduled and working for 1 year+ but and have only just started playing up.

 

[DrSql]

If you havent changed any chnages at your end, did you check with Network Team any policies has been pushed down.

Dr.Sql
Good Luck.

 

[Sweenster]

They say nothing that would have an impact and things did run OK after these changes were made.

-Changed memory allocation to Applications rather than Background Services.
-Changed SQLServer.exe it run in "Normal" priority mode.