Hi,
I am having problems running the sample code that is part of this
email. The code works fine for DTS Packages that do not have any OLAP
object tasks. When I try to retrieve the properties information for
the OLAP object task, it fails. Any other object tasks it works fine.
My objective is to log all statistics for each steps in a DTS Package
to an Oracle table. This sample code obtains a process id for the
current running DTS Package and a task id for each completed step.
P.S.
I have replace the Data Source, User ID, and Password with Xs.
'**********************************************************************
'** Script Name..: xTaskUpdateTaskInfo.vbs
'** DTS Package..: All
'**
'** Comments.....: The following code will log each of the steps that
'** exist in the current DTS Package.
'**
'**
'***********************************************************************
'***********************************************************************
'** CommandTypeEnum Values
'***********************************************************************
Const adCmdUnKnown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoreProc = &H0004
Function Main()
Dim sStepName, sTaskName, sCurrentStepName, sSQL, sProcessStatus,
sStepStatus
Dim sStartDate, sStartTime, sEndTime, sCurrStartDate, sCurrStartTime,
sCurrEndTime
Dim oPackage, oStep, oTask, oProperty, oConn, oRecordSet, oCmd
Dim nCntr, nProcessID, nTskCntr, nCurrentCntr, nIDValue, nDuration
Dim vRecordsProcessed
Dim msg
Dim arrDateTime
arrDateTime = NULL
'***********************************************************************
'** Format Start Date and Time
'***********************************************************************
arrDateTime = Split(NOW," ")
sCurrStartDate = Day(NOW) & "-"
sCurrStartDate = sCurrStartDate & MonthName(Month(NOW),True) & "-"
sCurrStartDate = sCurrStartDate & Year(NOW)
sCurrStartDate = "'" & sCurrStartDate & "'"
sCurrStartTime = "'" & arrDateTime(1) & " " & arrDateTime(2) & "'"
'***********************************************************************
'** Create ADO objects
'***********************************************************************
Set oConn = CreateObject("ADODB.Connection")
Set oRecordSet = CreateObject("ADODB.Recordset")
Set oCmd = CreateObject("ADODB.Command")
'***********************************************************************
'** Set MSDAORA connection properties
'***********************************************************************
oConn.Open "Provider=MSDAORA;Data Source=xxxxxxxx;User
ID=xxxxx;Password=xxxxx"
'***********************************************************************
'** Set the active connection to the target database ADO Object
'***********************************************************************
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdText
'***********************************************************************
'** Prepare the SQL statement for retrieving a unique task id
'***********************************************************************
sSQL = "SELECT NVL(MAX(TASK_ID),0) AS IDVALUE FROM ETL_TASK_LOG"
'***********************************************************************
'** Execute the SQL statement and retrieve the data
'***********************************************************************
oRecordSet.Open sSQL, oConn
'***********************************************************************
'** Move the data set cursor to the first record
'***********************************************************************
If Not(oRecordSet.EOF and oRecordSet.BOF) Then
oRecordSet.MoveFirst
End If
nIDValue = oRecordSet.Fields("IDVALUE")
DTSGlobalVariables("StepID").Value = CLng(nIDValue)
'***********************************************************************
'** Close object handle
'***********************************************************************
oRecordSet.Close
'***********************************************************************
'** Find the current Process ID for this package
'** -- Prepare the SQL statement
'***********************************************************************
sProcessStatus = "'" & "RUNNING" & "'"
sSQL = "SELECT PROCESS_ID FROM ETL_PROCESS_LOG WHERE PROCESS_STATUS =
" & sProcessStatus
'***********************************************************************
'** Execute the SQL statement and retrieve the data
'***********************************************************************
oRecordSet.Open sSQL, oConn
'***********************************************************************
'** Move the data set cursor to the first record
'***********************************************************************
If Not(oRecordSet.EOF and oRecordSet.BOF) Then
oRecordSet.MoveFirst
End If
nProcessID = oRecordSet.Fields("PROCESS_ID")
DTSGlobalVariables("ProcessID").Value = CLng(nProcessID)
'***********************************************************************
'** Close object handle
'***********************************************************************
oRecordSet.Close
'***********************************************************************
'** Get the handle to the current DTS Package
'***********************************************************************
Set oPackage = DTSGlobalVariables.Parent
sCurrentStepName = "Update Task Info"
'***********************************************************************
'** Retrieve log info for all Steps in the current package
'***********************************************************************
For nCntr = 1 to oPackage.Steps.Count
If Not(sCurrentStepName = oPackage.Steps(nCntr).Description) Then
nCurrentCntr = NULL
sStepStatus = NULL
'***********************************************************************
'** Increment Step ID counter
'***********************************************************************
DTSGlobalVariables("StepID").Value =
DTSGlobalVariables("StepID").Value + 1
'***********************************************************************
'** Retrieve the description for the step
'***********************************************************************
sStepName = "'" & oPackage.Steps(nCntr).Description & "'"
'***********************************************************************
'** Find the task for the current step
'***********************************************************************
For nTskCntr = 1 to oPackage.Tasks.Count
If oPackage.Tasks(nTskCntr).Name =
oPackage.Steps(nCntr).TaskName Then
Exit For
End If
Next
'***********************************************************************
'** Retrieve the name for the task
'***********************************************************************
sTaskName = oPackage.Tasks(nTskCntr).Name
'***********************************************************************
'** Set remaining variables for completed steps and log to
database
'***********************************************************************
If oPackage.Steps(nCntr).ExecutionStatus = 4 Then
'***********************************************************************
'** Retrieve the number of rows processed for this step
'***********************************************************************
vRecordsProcessed = 0
For Each oProperty In oPackage.Tasks(nTskCntr).Properties
If oProperty.Name = "RowsComplete" Then
If IsNull( oProperty.Value ) Then
vRecordsProcessed = "'" & "NULL" & "'"
Else
vRecordsProcessed = oProperty.Value
End If
Exit For
End If
Next
'***********************************************************************
'** Set the completed status for the current step
'***********************************************************************
sStepStatus = "'" & "COMPLETED" & "'"
'***********************************************************************
'** Format Start Date and Time
'***********************************************************************
'msgbox "StartTime...: " & oPackage.Steps(nCntr).StartTime
arrDateTime = Split(oPackage.Steps(nCntr).StartTime," ")
sStartDate = Day(oPackage.Steps(nCntr).StartTime) & "-"
sStartDate = sStartDate &
MonthName(Month(oPackage.Steps(nCntr).StartTime),True) & "-"
sStartDate = sStartDate & Year(oPackage.Steps(nCntr).StartTime)
sStartDate = "'" & sStartDate & "'"
sStartTime = "'" & arrDateTime(1) & " " & arrDateTime(2) & "'"
arrDateTime = NULL
'***********************************************************************
'** Format End Date and Time
'***********************************************************************
'msgbox "FinishTime...: " & oPackage.Steps(nCntr).FinishTime
arrDateTime = Split(oPackage.Steps(nCntr).FinishTime," ")
sEndDate = Day(oPackage.Steps(nCntr).FinishTime) & "-"
sEndDate = sEndDate &
MonthName(Month(oPackage.Steps(nCntr).FinishTime),True) & "-"
sEndDate = sEndDate & Year(oPackage.Steps(nCntr).FinishTime)
sEndDate = "'" & sEndDate & "'"
sEndTime = "'" & arrDateTime(1) & " " & arrDateTime(2) & "'"
nDuration = CLng(oPackage.Steps(nCntr).ExecutionTime)
If nDuration <= 0 Then
nDuration = CLng(1)
End If
msg = "Process ID....: " &
DTSGlobalVariables("ProcessID").Value & vbCrLf
msg = msg & "Step Name.....: " & sStepName & vbCrLf
msg = msg & "Step ID.......: " &
DTSGlobalVariables("StepID").Value & vbCrLf
msg = msg & "Start Date....: " & sStartDate & vbCrLf
msg = msg & "Start Time....: " & sStartTime & vbCrLf
msg = msg & "End Time......: " & sEndTime & vbCrLf
msg = msg & "Status........: " & sStepStatus & vbCrLf
msg = msg & "Duration(secs): " & nDuration & vbCrLf
msg = msg & "Row Processed.: " & vRecordsProcessed & vbCrLf
msgbox msg
'***********************************************************************
'** Prepare the Insert statement for logging the task log record
'***********************************************************************
oCmd.CommandText = "INSERT INTO ETL_TASK_LOG " & _
"(" & _
"PROCESS_ID, " & _
"TASK_ID, " & _
"TASK_NAME, " & _
"TASK_STATUS, " & _
"ROW_PROCESSED, " & _
"START_DATE, " & _
"START_TIME, " & _
"END_TIME, " & _
"ELAPSED_TIME_SEC) " & _
"VALUES (" & DTSGlobalVariables("ProcessID").Value & "," & _
DTSGlobalVariables("StepID").Value & "," & _
sStepName & "," & _
sStepStatus & "," & _
vRecordsProcessed & "," & _
sStartDate & "," & _
sStartTime & "," & _
sEndTime & "," & _
nDuration & ")"
msgbox oCmd.CommandText
'***********************************************************************
'** Insert and commit the task log to the databse
'***********************************************************************
oCmd.Execute , ,adExecuteNoRecords
oCmd.CommandText = "Commit"
oCmd.Execute , ,adExecuteNoRecords
End If
End If
Next
'***********************************************************************
'** The following section will log statistics for this step...The
'** reason why this is being done at exit is because some of statistic
'** are not generated for steps that are waiting or still running.
'***********************************************************************
'***********************************************************************
'** Increment Step ID counter
'***********************************************************************
DTSGlobalVariables("StepID").Value =
DTSGlobalVariables("StepID").Value + 1
sStepName = "'" & sCurrentStepName & "'"
sStartDate = sCurrStartDate
sStartTime = sCurrStartTime
arrDateTime = NULL
'***********************************************************************
'** Format End Date and Time
'***********************************************************************
arrDateTime = Split(NOW," ")
sEndDate = Day(NOW) & "-"
sEndDate = sEndDate & MonthName(Month(NOW),True) & "-"
sEndDate = sEndDate & Year(NOW)
sEndDate = "'" & sEndDate & "'"
sEndTime = "'" & arrDateTime(1) & " " & arrDateTime(2) & "'"
sStepStatus = "'" & "COMPLETED" & "'"
nDuration = CLng(1)
vRecordsProcessed = 0
'***********************************************************************
'** Prepare the Insert statement for logging the task log record
'***********************************************************************
oCmd.CommandText = "INSERT INTO ETL_TASK_LOG " & _
"(" & _
"PROCESS_ID, " & _
"TASK_ID, " & _
"TASK_NAME, " & _
"TASK_STATUS, " & _
"ROW_PROCESSED, " & _
"START_DATE, " & _
"START_TIME, " & _
"END_TIME, " & _
"ELAPSED_TIME_SEC) " & _
"VALUES (" & DTSGlobalVariables("ProcessID").Value & "," & _
DTSGlobalVariables("StepID").Value & "," & _
sStepName & "," & _
sStepStatus & "," & _
CLng(vRecordsProcessed) & "," & _
sStartDate & "," & _
sStartTime & "," & _
sEndTime & "," & _
nDuration & ")"
'***********************************************************************
'** Insert and commit the task log record to the databse
'***********************************************************************
oCmd.Execute , ,adExecuteNoRecords
oCmd.CommandText = "Commit"
oCmd.Execute , ,adExecuteNoRecords
'***********************************************************************
'** Close object handle
'***********************************************************************
oConn.Close
Main = DTSTaskExecResult_Success
End Function
Extracting log information for DTS Packages, Steps, and Error in a ActiveX Script