General discussion


SQL2000: DTS Query Task - Return Value

By ronbracale ·
In SQL Server 2000, in DTS, a SQL Query Task executes a stored procedure that returns 0 or -1. Either value causes a Workflow property of success, but I require two different actions. I do not want to cause the package to fail (for example with raiseerror). I can conditionally check the return of the stored procedure and need to know what to set in order to have the DTS workflow properties of success and failure lead to the next steps.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

by StreamLogic In reply to SQL2000: DTS Query Task - ...


To do this, I had to write a custom function which was called within an ActiveX task. This function created a SQL Query Task using the API, set the SQL, executed it, and returned a recordset or value.

The key, once the 0 or -1 comes back, is to disable and enable the tasks which should run next. This is somewhat difficult to explain in words. For example, if a 0 comes back, you would write in the ActiveX:

task_to_disable.enabled = false
task_to_enable.enabled = true
task_to_enable.waiting = true

I'm not sure if this syntax is 100%, since I don't have DTS open in front of me.

The key I have found is writing the custom function to execute dynamic SQL via ActiveX and use conditional logic within the ActiveX script to "branch" the logic.

Hope this helps,

StreamLogic Inc.

Related Discussions

Related Forums