How can I make a DTS step optional?

Question

How can I make a DTS step optional?

NigelFN

Default port

Points: 1467
More actions
January 30, 2003 at 11:01 am

#151787

I currently have a DTS package with multiple steps of different types (ActiveX script, SQL, Process Cube, run DTS package) and parameters for each of these steps are set dynamically within the package.
I would like to make the running of each of the steps optional, using an ActiveX script task to set this at the beginning.
I have tried to do this by programmatically setting the DisableStep property of a Step, but then there are problems with the workflow and the Step following a disabled Step is not run.
I have tried using DTSStepExecStat_Inactive to switch all steps off, the use DTSStepExec_Stat_Waiting when I want a Step to run - but then it does things in any-old-order and sometimes causes nasty errors (mmc has been known to die).
Another thought is to not only set the DisableStep property, but to change the Precedence details for the Step that follows (once you've worked out what that is). This seems a bit complex / messy.
Can anyone help by suggesting how they would approach such a problem?
Many thanks in advance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

philcart SSC-Forever Points: 47796 More actions · Answer 1

I'd go with Disabling the steps and setting the Precedence details. At least that way you can be sure that things will run in the right order.

I've had some weird problems setting the step status. Yes it will be complex, but that's just the way it is with DTS sometimes.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 2

If you want successive steps to run why not set the step to DTSStepExec_Stat_Waiting ( already succesfully run )

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 3

If you want successive steps to run why not set the step to DTSStepExec_Stat_Success ( already succesfully run )

ajkeenan SSC Rookie Points: 40 More actions · Answer 4

You could set global variable flags in the package and use an ActiveX script in the Workflow properties for a task to determine whether that task should execute or not.

One example I have used is [task 1] has a completion precedence workflow to [task 2]. In the Workflow properties for [task 2], I have used the 'Options' tab and set the 'Use ActiveX script' check box. I have clicked the 'Properties' button and used the following script:

---------------------------

Function Main()

If DTSGlobalVariables("run_flag").Value > 0 THEN

'Execute task 2

Main = DTSStepScriptResult_ExecuteTask

Else

'Do not execute task 2.

Main = DTSStepScriptResult_DontExecuteTask

END IF

End Function

---------------------------

With the above, [Task 2] only executes when the global variable has a value greater than zero.

Andrew Keenan

http://www.iocore.com.au

Andrew Keenan
Consultant, Business Intelligence
Technology Services
KAZ
E andrew.keenan@kaz-group.com

NigelFN Default port Points: 1467 More actions · Answer 5

Thanks for your suggestions.

AnzioBake - Sorry, but I don't really understand your suggestion - could you flesh it out with a bit more detail for the example?

Andrew, the use of Workflow ActiveX scripts was news to me - very useful. Unfortunately, using them doesnt solve the problem of running the following steps. If you set '_DontExecuteTask', the following task in sequence doesn't run - because this task didn't complete. I think that this is a better place to put the decision code, but still need to reset Precedence stuff, unless you can tell me otherwise.

Thanks again for any further comments.

philcart SSC-Forever Points: 47796 More actions · Answer 6

Have you tried setting the StepExecutionStatus to 'DTSStepExecStat_Completed'?

I've only done the Inactive and Waiting myself. Just thought that this might simplify a problem I'm having. Will be trying it out later today.

Thanks

Phill Carter

--------------------
Colt 45 - the original point and click interface

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 7

By setting DTSExecStat_Success the DTS would execute subsequent steps in the the order originally specified as if the step which you skipped ( and set DTSExecStat_Success ) has run succesfully.

Suggestion by phillcart may not work if precedence id set to "On Success" or "On Failure"

NigelFN Default port Points: 1467 More actions · Answer 8

Hi AnzioBake,

As I see it, you can only successfully use DTSExecStat_Success at the end of an ActiveX Script step. I need to set other types of steps off also - e.g. Process Cube, run DTS Package.

I have tried setting this value within the 'Workflow Properties | Use ActiveX Script' section and it does not work.

Unfortunately, these things do not work as they sound like they should in the documentation.

Is there some other way that you have used to get it to work? Your assistance is appreciated!

Phil, looking forward to hearing your results - it didn't work for me!

philcart SSC-Forever Points: 47796 More actions · Answer 9

Ok here's what I came up with. An important thing to note is that you need to set the Precedence Constraints for the next step in the sequence.

I set up 5 ActiveX Scripts to execute one after the other. The first step randomly set a flag to skip the steps or not. The 2nd and 4th steps had the script below ( with different step names ) in the Workflow Properties section to modify the properties of the 3rd and 5th steps. It worked flawlessly time after time.

However, I did find one problem. Whenever there was more than one Precedence Constraint ( eg: Step 5 waits on Steps 3 & 4), execution just seemed to hang and started to use 100% cpu.

Function Main()

Dim oPkg, oStep, iCntr

Set oPkg = DTSGlobalVariables.Parent

' Set step object to name of following step

Set oStep = oPkg.Steps("DTSStep_DTSActiveScriptTask_6")

iCntr = 1

' loop through the precendence constraint collection

Do While iCntr <= oStep.PrecedenceConstraints.Count

' check if current item refer to this step

If oStep.PrecedenceConstraints(iCntr).StepName = "DTSStep_DTSActiveScriptTask_2" Then

If < Your skip condition > = True Then

oStep.PrecedenceConstraints(iCntr).PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus

oStep.PrecedenceConstraints(iCntr).Value = DTSStepExecStat_Inactive

Main = DTSStepScriptResult_DontExecuteTask

Exit Do

Else

oStep.PrecedenceConstraints(iCntr).PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult

oStep.PrecedenceConstraints(iCntr).Value = DTSStepExecResult_Success

Main = DTSStepScriptResult_ExecuteTask

Exit Do



End If

End If

iCntr = iCnt + 1

Loop



Set oStep = Nothing

Set oPkg = Nothing

End Function

Thanks

Phill Carter

Edited by - phillcart on 02/05/2003 12:43:47 AM

Edited by - phillcart on 02/05/2003 12:45:23 AM

Edited by - phillcart on 02/05/2003 12:48:25 AM

--------------------
Colt 45 - the original point and click interface

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 10

I knew I did this somewhere but had to go play around again last night.

Setting The execution status to completed would still fire "on failure" steps

You can put empty activeX scripts after the optional task, disable the optional task and then change just one set of precedence steps Or Drop Precedence when you want to run the step.

Precedence values are 0 = success, 1 = failure, 4 = completion.

Test Package / code

Create this simple Package

DTSTask_DTSActiveScriptTask_1

DTSTask_DTSActiveScriptTask_2

DTSTask_DTSActiveScriptTask_3

DTSTask_DTSActiveScriptTask_4

DTSTask_DTSActiveScriptTask_5

DTSExecuteSQLTask_1

They do not have to do anything you can just watch the execution.

DTSActiveScriptTask_2

Executes on Success of DTSExecuteSQLTask_2

DTSActiveScriptTask_3

Executes on Failure of DTSExecuteSQLTask_2

DTSActiveScriptTask_4

Exe on Completion of DTSExecuteSQLTask_2

DTSActiveScriptTask_5

Exe on Success of DTSActiveScriptTask_4

DTSExecuteSQLTask_2

exec on success of DTSActiveScriptTask_1

DTSTask_DTSActiveScriptTask_1

Code:

Dim oPackage

Set oPackage = DTSGlobalVariables.Parent

'Disabble the step

oPackage.Steps("DTSStep_DTSExecuteSQLTask_1").DisableStep = 1

'Reset precedence

oPackage.Steps("DTSStep_DTSActiveScriptTask_3").PrecedenceConstraints(1).StepName="DTSStep_DTSActiveScriptTask_1"

oPackage.Steps("DTSStep_DTSActiveScriptTask_3").PrecedenceConstraints(1).Value = 0

Main = DTSTaskExecResult_Success

I hope this is clear enough for you to understand.

NigelFN Default port Points: 1467 More actions · Answer 11

Thanks for your responses, Phil and Anzio - very clear.

Unfortunately, I was hoping to find a solution without changing the Precedence of following tasks - I knew about this being an issue and mentioned it in my original post.

This starts getting messy when multiple steps are optional and it's something I would like to avoid.

I've decided to re-write the package in T-SQL now, just calling DTS for the bits that need to be in DTS (optionally, of course).

Thanks again for your help.