General discussion

  • Creator
  • #2257509

    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.

All Comments

  • Author
    • #3203305

      Reply To: SQL2000: DTS Query Task – Return Value

      by streamlogic ·

      In reply to SQL2000: DTS Query Task – Return Value


      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.

Viewing 0 reply threads