Hi,
I'm running an execute sql task in DTS on SQL 2k, that executes a sproc*in a FOR loop. When there are no issues, the step executes, and the active-X steps surrounding it allow the loop to work perfectly. There is potential for the sproc to return a non-zero issue, primarily due to no connection to the server being querried in that step of the loop. On failure, I would like to just execute a sql task (sproc that writes to logging db) in the workflow, and return to the loop. In simulating the test, I pass a bad password, so that the query in the sproc fails. (The secondary issue is that the query does not return a non-zero error code, which is another issue that I will post elsewhere). However, when the task fails, the failure workflow is followed, that SQL task executes, but upon success or completion, the workflow pointing to the active x task does not run, the loop does not occur, and then the task shuts down.

I would like it to continue to loop, so that info on the other servers can be obtained.

Incidently, I set workflow on the exec sql step (that's failing) to continue looping on completion, but there's two problems:
1) erratic results.
2) I have a message box querrying the status of the exec sql statement and that step's DTSExecResult shows as being equal to 4.*The Enumeration values listed on MSDN are only 0-3.

Since the step failed, is there a status that needs to be programmatically reset, before setting
that step's***ExecutionStatus equal to DTSStepExecStat_Waiting
in the active-x to loop the task?

If I could make this work, how could I use the *active-x loop step *to not only clear whatever status is holding up the re-processing, but checking programmatically for the result (failure), so that I could call the logging exec SQL step, reset ing the parameter variables, and continuing to execute the loop?

Thanks!

From http://www.developmentnow.com/g/103_...server-dts.htm

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/