DTS package sometimes runs all steps

[mingdynasty]

I have a DTS package that is scheduled to run twice a day. About a month ago, the morning jobs begain having intermittent problems. It would stop at a step and not continue. I can start the package manually and it runs fine manually.

The package consisted of several steps in the beginning that executed some stored procedures. The heavy lifting is at the beginning. At the end, it runs VBScript to create some files.

I turned on the logging of the package. From the log, it appears every step runs as expected but I can tell certain steps are being missed. I put in every step commands that insert a timestamp record every time the package reaches certain points of execution (i.e. "Beginning Step 15"). It is evident that it does not run everything because certain points are missing. Anybody else have any ideas how to track this problem further?

 

[nigelrivett]

Instead of using a dts package run a stored proc. It is much simpler and you should find it easier to maintain. You can call a package for the output but it's probably easier to use bcp.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[mingdynasty]

It would be nice if I could keep everything in a stored procedure but I can't. There are parts that need to run in VBScript. Where I need help is troubleshooting techniques.

 

[nigelrivett]

>> There are parts that need to run in VBScript
I doubt that but ....

You have already done the obvious things. I have never seen dts log that it has executed a step and actually hasn't - it may be getting an error which is stopping your trace from being writeen.

You might try loading the package, running it then stepping through all the steps logging the state.
Also check the workflow carefully to make sure you haven't missed some dependencies that is causing the package to terminate early - this would cause differences depending on speed of executionand could cause what you are seeing in the two environments.

Try it set to terminate on first error and to single thread if you haven't done that already.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.