March 26, 2003 at 8:48 pm
is there any way to use Global variable with SQL 7? My DTS pkg is working fine with SQL 2000, but I have used SQL 2000 specific objects (ExecuteSQLTask2,step2) in my code.I am attaching my code that works correctly if I have SQL2000 installed on the machine.any help is appericiated.
Private Sub TaskReadHostDBFailed()
'Manish Sept 19th 2002
'---------------------------------------------------------------------------
' Task to read dp_system_config
'---------------------------------------------------------------------------
Dim oTask As DTS.Task
Dim oCustomTask As DTS.ExecuteSQLTask2
On Error GoTo TaskReadHostDBFailed_error
Log_Function 0, "TaskReadHostDBFailed", SS & " DataPass DB = " & DPDataBase & _
" System = " & Trim(System_Name)
'Create an ExecuteSQLTask and initialize the CustomTask object
Set oTask = moPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask = oTask.CustomTask
'#{{not using EventLogger majain 01-24-2003 12:23:53}}
'DPEvents.LogEvent "TaskReadHostDBFailed", "", 0, "", "", Err.Number, Err.Description, _
"DataPass DataBase Name = " & DPDataBase
With oCustomTask
.Name = "TaskReadHostDBFailed"
.Description = "Read Host_DB_Failed from Dp_System_Config"
.SQLStatement = "SELECT host_db_failed = (CASE WHEN host_db_failed = 'True' THEN 1 WHEN host_db_failed = 'False' THEN 0 END) from " _
& Trim(DPDataBase) & ".dbo.dp_system_config Where System_Name=" & "'" & Trim(System_Name) & "'"
.ConnectionID = EConnectionIds.datapass 'Connection on which to execute
.CommandTimeout = 0 'Length of time to wait
.OutputGlobalVariableNames = """SkipTask"""
.OutputAsRecordset = False
End With
moPackage.Tasks.Add oTask
Set oCustomTask = Nothing
Set oTask = Nothing
Log_Function 0, "TaskReadHostDBFailed", SE
Exit Sub
TaskReadHostDBFailed_error:
Log_Function 3, "TaskReadHostDBFailed", " DataPass DB = " & DPDataBase & _
" System = " & Trim(System_Name)
End Sub
Private Sub StepCheckHostDBFailed()
Dim oStep As DTS.step2
On Error GoTo StepCheckHostDBFailed_Error
Log_Function 0, "StepCheckHostDBFailed", SS
Set oStep = moPackage.Steps.New
'Manish Sept 19th 2002
'---------------------------------------------------------------------------
' to check host_db_failed and skip task if it's true
'---------------------------------------------------------------------------
oStep.Name = "CheckHostDBFailed"
oStep.Description = "Check Host_DB_Failed Flag"
oStep.ExecutionStatus = 1
oStep.TaskName = "TaskReadHostDBFailed"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ActiveXScript = "' CheckHostDBFailed" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & "Option Explicit" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & "Function Main()" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Dim oPkg, oStep,bHostDbFailed" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " ' Get the FinalTask Step" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Set oPkg = DTSGlobalVariables.Parent" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Set oStep = oPkg.Steps(""CheckHostDBFailed"")" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " " & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " If DTSGlobalVariables(""SkipTask"").Value Then " & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " ' Update the following task's precedence constraint" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " ' - Execution status of inactive" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " oStep.PrecedenceConstraints(1).PrecedenceBasis = _" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " DTSStepPrecedenceBasis_ExecStatus" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " oStep.PrecedenceConstraints(1).Value = _" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " DTSStepExecStat_Inactive" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Main = DTSStepScriptResult_DontExecuteTask" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Else" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " ' Update the following task's precedence constraint" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " ' - Execution result of success" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " oStep.PrecedenceConstraints(1).PrecedenceBasis = _" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " DTSStepPrecedenceBasis_ExecResult" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " oStep.PrecedenceConstraints(1).Value = _" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " DTSStepExecResult_Success" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Main = DTSStepScriptResult_ExecuteTask" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " End If" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " " & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & " Set oStep = Nothing" & vbCrLf
oStep.ActiveXScript = oStep.ActiveXScript & "End Function"
oStep.ScriptLanguage = "VBScript"
oStep.FunctionName = "Main"
oStep.AddGlobalVariables = True
'oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
'oStep.IsPackageDSORowset = False
'oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False
moPackage.Steps.Add oStep
Set oStep = Nothing
Log_Function 0, "StepCheckHostDBFailed", SE
Exit Sub
StepCheckHostDBFailed_Error:
Log_Function 3, "StepCheckHostDBFailed", "Step Build Error"
End Sub
April 1, 2003 at 6:59 am
The following discussion covers this area pretty well. Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply