Executing a function inside a DTS

Question

Post reply

Executing a function inside a DTS

Oscar Hernández

SSC Veteran

Points: 200
More actions
August 1, 2005 at 9:37 am

#165536

Hi,

I´executing a function inside an "Execute SQL task" object. If the returning value of the function is 'KO' then I don´t want the DTS continues with the workflow. How can I do that?
Thanks in advance,

Regards

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

This should point you in the right direction. Well written and easy to follow:

http://www.sqldts.com/default.aspx?214

------------------------------
The Users are always right - when I'm not wrong!

Joe Williams-227299 SSC Journeyman Points: 87 More actions · Answer 2

You can also use RAISERROR to force an error condition in dts.

declare @FUNCTIONTEST varchar(2)

set @FUNCTIONTEST = 'KO'

IF @FUNCTIONTEST='KO' RAISERROR ('DO NOT PROCESS',11,1)

The above lines will create an error condition in the dts package, and it will follow the "On Error" workflow line.

You can read more about RAISERROR in Books Online.

Rayfuss Valued Member Points: 60 More actions · Answer 3

Another thing you can do is get the result of that function into a global variable, and in the workflow properties of that Execute SQL Task, you specify that if the content of that variable is "KO", the workflow has to stop or skip as many tasks as you want.

hope this helps...

Rayfuss.-

http://www.e-techcafe.com

Oscar Hernández SSC Veteran Points: 200 More actions · Answer 4

thanks to all!! There are many ways... Let´s go with the Ray´s one. How can I specify that if the content of the variable is 'KO', the workflow has to stop? I can see nothing in the workflow properties of the "Execute SQL task". Perhaps in ActiveX commands? In that case, any example to do it?

Meanwhile I´m going to test the RAISE option...

Thanks,

Regards

Vítor Nuno Viana Ferreira SSC Veteran Points: 255 More actions · Answer 5

You can use an ActiveX after your SQL Task and if your Global variable is 'Ok' then "Main = DTSTaskExecResult_Success" else "Main = DTSTaskExecResult_Failure" .

If you don't want to your DTS to terminate in error you can disable you next step in Workflow!

Hope this helps,

Vítor

Oscar Hernández SSC Veteran Points: 200 More actions · Answer 6

I have two questions:

FIRST

I have your example:

if Global variable is 'Ok' then

Main = DTSTaskExecResult_Success

else

Main = DTSTaskExecResult_Failure

end if

I think that activex script is executed before the dts step, then, with the example above, is the step going to be executed and its result is goint to be always a "Failure", or automatically the statement " DTSTaskExecResult_Failure" cancel the execution and returns a "Failure" for the workflow?

SECOND

I have my example:

Funtion Main ()

Dim v_estado

set v_estado=DTSGlobalVariables("vg_valor_out").value

IF (v_estado='OK') THEN

Main=DTSStepScriptResult_ExecuteTask

ELSE

Main=DTSStepScriptResult_DontExecuteTask

END IF

End Funtion

the statement "DTSStepScriptResult_DontExecuteTask" stop the execution of the next tasks in the workflow?or is simply a jump if I have another tasks later? the return value for the step is "on failure" or "on success" after a "DTSStepScriptResult_DontExecuteTask" statement?

thanks vitor for your help

Regards

Rayfuss Valued Member Points: 60 More actions · Answer 7

for the first question, a step failure doesn't mean a package failure, you can define that in your package/step properties.

for the second one, the activexscript on the workflow is to define specific logic for the next step execution, it doesn't mean that it has to be success or failure. The DTSStepScriptResult_DontExecuteTask means that the second task in the workflow won't be executed, but you can have an alternate workflow to execute whatever task u want.

hope it helps...

Rayfuss.-

http://www.e-techcafe.com