Web Development

Ask something
Discussion Locked

SQL2000: DTS Query Task - Return Value

0 helpful
1 reply

by ronbracale

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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.

1 reply

by streamlogic

Ron,

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,
Curtis

StreamLogic Inc.
http://www.StreamLogicInc.com

This discussion is closed to new replies.