Change Constraints/Path on the fly

• Sean Gough

  SSC Journeyman

  Points: 79

  More actions

  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

• DSP-120308

  Hall of Fame

  Points: 3959

  More actions

  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

• Sean Gough

  SSC Journeyman

  Points: 79

  More actions

  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