June 19, 2003 at 9:31 am
Hi
is it possible to control workflow by returned values from procedures called in an execute sql task, rather than just success, failure or completion?
thanks in advance
Ryan
June 19, 2003 at 10:09 am
Not that I know. The only way I've found is to use an Active X Task to call the sproc and then alter the package dynamically or use the Active X task as the completion item, check a result someone and then determine the next step.
What are you looking for?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 19, 2003 at 2:46 pm
Yes, you can if you think it is worth the effort. You will have to use some Active X scripts but not neccessarily Active X script tasks. I will try to describe this as simply as I can. For this example you will need 3 execute sql tasks and one connection.
First create a global variable called gvtest
The first task will have the following code in it:
SELECT 1
click parameters, output parameters, row value, click in box called output global variables, and select gvtest from the drop down box. Then click OK and OK again.
created the following SP:
CREATE PROCEDURE sptest AS
RETURN 10
Put the following code in the next 2nd execute sql task:
DECLARE @test-2 int
EXEC @test-2 = sptest
SELECT @test-2
click parameters, output parameters, row value, click in box called output global variables, and select gvtest from the drop down box. Then click OK and OK again.
You must connect the first task with the 2nd task to ensure the global variable gets initialized before the second task runs.
For this example it is unimportant what is in the 3rd task. You can put SELECT 2 if you like.
Now right click the 3rd task, click workflow, workflow properties, options, click inside the box labeled use ActiveX script, click properties, and delete all code found and put this in there:
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Function Main()
count = 1
Do
count = count + 1
Loop While count < 100000
Do
Loop While DTSGlobalvariables("gvtest").value <> 10
If DTSGlobalvariables("gvtest").value = 10 Then
Main = DTSStepScriptResult_ExecuteTask
ELSE
Main = DTSStepScriptResult_DontExecuteTask
END IF
End Function
Now save and then execute the DTS package.
Please note that the first loops only purpose is to give the 1st task time to initialize the global variable, otherwise the last value used will get used again and that is where you must be extremely cautious if you plan to use what I have described above.
Robert W. Marda
SQL Programmer
bigdough.com
The world’s leading capital markets contact database and software platform.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
June 20, 2003 at 3:01 am
In reply to Steve:
this is related to my previous post on management of temporary files created in an sp and then called by DTS:
the SP in question in that post is used to look for the presence of a file in a certain location, and returns a value of 0 or 1 depending on the result. I wanted to use the return value to signal the continuation or controlled error out of the rest of that package. If you know of a better way then I would be most grateful....
rgds
ryan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply