Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Import/Export (DTS) and Replication (2000)  DTS looping issue/step failure

Author  Topic  pchak Starting Member 2 Posts Posted - 2009-08-03 : 22:29:20 Hi, I haven't had much luck with this issue. 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 <step>.DTSExecResult = 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 calling DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_3"). _ ExecutionStatus = 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!