Extracting log information for DTS Packages, Steps, and Error in a ActiveX Script 
Author Message
 Extracting log information for DTS Packages, Steps, and Error in a ActiveX Script

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



Tue, 19 Oct 2004 02:02:01 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Sample Code for extracting package and step log info in ActiveX Script

2. ActiveX scrip executed basen on success of 8 independent tasks within one DTS

3. Where to look for the log to find the execution times of steps in dts packages

4. 2 step DTS package - 2nd step not executing

5. Error Handling DTS Package Steps

6. Incomplete DTS log -- Some steps are not logged

7. Incomplete DTS log -- Some steps are not logged

8. Cannot get error information in DTS package VBScript

9. Capture Error in DTS Package, From Inside DTS Package

10. Error executing a DTS package from another DTS package

11. DTS ActiveX Scripting to find previously called step?

12. ActiveX step running different scheduled than on DTS Designer


 
Powered by phpBB® Forum Software