Post reply

Change Constraints/Path on the fly

  • February 17, 2005 at 9:42 am

    #61532

    I have a package that has about 20 steps.  I won't get into details, but here are the basics...

    Step #1 is an Execute SQL Task that creates a log.

    Step #2 is an Execute SQL Task that populates a boolean variable based on the results of a stored procedure.

    Steps #3-18 do a bunch of processing.

    Step #19 is ActiveX script that sends an email notification.

    Step #20 is an Execute SQL Task that updates the log.

    Now, the issue is that Steps 3-18 should only happen if the boolean evaluates to TRUE, while Steps 19 & 20 should happen in all situations. 

    I am pretty sure that it's possible to alter the path/contraints on the fly (I have some other packages with ActiveX determining whether to execute individual steps or not) but I am struggling with how exactly I should do it. 

    Any help would be greatly appreciated!

    -Sean

  • February 17, 2005 at 10:17 am

    #542875

    Sean,

    This link is an example of constraint manipulation

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

    Good Luck,

    Darrell

  • February 17, 2005 at 11:27 am

    #542901

    Oddly enough I had just found that article and used part of it's logic and part of another on skipping a task to do what I needed. 

    In case anyone is interested, the solution was to use an ActiveX script in step #3's workflow that both determined whether it should run and modified the contraints around step #19.  Like this...

    Function Main()
 Dim objPkg, objStep

     ' get the mail step
 Set objPkg= DTSGlobalVariables.Parent
 Set objStep= oPkg.Steps("DTSStep_DTSActiveScriptTask_2")
 
 If (DTSGlobalVariables("blnProcess")) Then
    'update the mail task's precedence constraint
    objStep.PrecedenceConstraints(1).PrecedenceBasis = DTSStepPrecedenceBasis_ExecResult
    objStep.PrecedenceConstraints(1).Value = DTSStepExecResult_Success
    Main = DTSStepScriptResult_ExecuteTask
 Else
    'update the mail task's precedence constraint
    objStep.PrecedenceConstraints(1).PrecedenceBasis = DTSStepPrecedenceBasis_ExecStatus
    objStep.PrecedenceConstraints(1).Value = DTSStepExecStat_Inactive Or DTSStepExecStat_Waiting
    objStep.Steps("DTSStep_DTSExecuteSQLTask_3").ExecutionStatus = DTSStepExecStat_Inactive
    Main = DTSStepScriptResult_DontExecuteTask
 End If
 Set objStep = Nothing
 Set objPkg = Nothing
End Function

    Thanks!

    -Sean

Viewing 3 posts - 1 through 2 (of 2 total)

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