Determine name of executing step
-
Hi All
Hope some one can aid me with a question that's causing me some angst.
Within an ActiveX script task, is there any method of determining the name of the step the activex script is executing in?
Thanks
Phill
--------------------
Colt 45 - the original point and click interface
-
I dont know how to directly, though that doesnt mean anything. You could set a global variable in each step, easy enough to do that way.
Andy
-
Andy,
That is just what I'm trying to do. But I can find no method of determining the current step.
Basically I want to log a status message to a file saying something like...
"Package Yada Yada: Step Yada Yada completed etc... "
The only way I've been able to do this was to manually set a variable, just like creating a constant. Now the scope of my package has grown and I want to use the same code in other scripts.
Surely I should be able to dynamically determine the name of the step that I'm in?
Thanks
Phill
--------------------
Colt 45 - the original point and click interface -
Might be, I just don't know how. Possibly there is some event you could trap. Unless you do find one all purpose terrific step execute event, you'll have to add code to each step to log the step execution, how much more code to just set the variable? Not disagreeing that it would be nice to have access to it, just thinking its not worth a lot of time...in my opinion of course:-)
Andy
-
Hi Phil
If in your package there is only one task executed in any given time - then in your script you can reference your Package.
Then traverse the steps and find out which one is in Progress and that one the one.
Below is fragment of the code:
'--------------
set objPackage = DTSGlobalVariables.Parent
blnCurrent = FALSE
strThisOneStepName = ""
' traverse all steps to
For Each objStep in objPackage.Steps
strName = objStep.Name
'find this step (this script belongs to)
IF objStep.ExecutionStatus = DTSStepExecStat_InProgress THEN
blnCurrent = TRUE
strThisOneStepName = objStep.Name
strThisOneTaskName = objStep.TaskName
strThisOneStepDesc = objStep.Description
END IF
NEXT
'--------------
Cheers
Tom
-
Ok Guys
Here is what I have at the moment. I probably should have pasted this in earlier
.Basically, from the current step I can determine the prior step via the PrecedenceConstraints collection. Then I can get info such as the ExecutionTime, Start Time, etc...
What I want to do is avoid having to set the Constant at the start of the function.
Should be simple shouldn't it?
Phill
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Const STEP_NAME = "Error Step 1"
Function Main()
On Error Resume Next
Dim nCntr
Dim oPkg
Dim oCurStp
Dim oErrStp
Dim nErrNo
Dim sErrSrc
Dim sErrTxt
Dim sPreStp
nCntr = 1
Set oPkg = DTSGlobalVariables.Parent
'Find Step
While oPkg.Steps(nCntr).Name <> STEP_NAME
nCntr = nCntr + 1
Wend
If IsObject(oPkg.Steps(nCntr)) = True Then
Set oCurStp = oPkg.Steps(nCntr)
nCntr = 1
' Find Name of Precedence Step
While oCurStp.PrecedenceConstraints(nCntr).PrecedenceBasis <> 1
nCntr = nCntr + 1
Wend
sPreStp = oCurStp.PrecedenceConstraints(nCntr).StepName
nCntr = 1
'Find Precedence Step
While oPkg.Steps(nCntr).Name <> sPreStp
nCntr = nCntr + 1
Wend
If IsObject(oPkg.Steps(nCntr)) = True Then
Set oErrStp = oPkg.Steps(nCntr)
MsgBox oErrStp.ExecutionStatus
MsgBox oErrStp.ExecutionResult
MsgBox oErrStp.ExecutionTime
MsgBox oErrStp.StartTime
MsgBox oErrStp.FinishTime
Set oErrStp = Nothing
Main = DTSTaskExecResult_Success
Else
MsgBox "Can't find precedence step name " & sPreStp
Main = DTSTaskExecResult_Failure
End If
Set oCurStp = Nothing
Else
MsgBox "Can't find step name " & STEP_NAME
Main = DTSTaskExecResult_Failure
End If
Set oPkg = Nothing
End Function--------------------
Colt 45 - the original point and click interface -
I have a really dumb question. Why not use the logging functionality built into DTS? What does your logging add that DTS logging doesn't have?
Thanks,
John
-
quote:
I have a really dumb question. Why not use the logging functionality built into DTS? What does your logging add that DTS logging doesn't have?Thanks,
John
Actually, it's not a dumb question. On reviewing my posts I see I neglected some info.
As we'll be dealing with a shared SQL Server cluster, we're working under pretty tight constraints as to execution of the DTS package.
We are not allowed to run DTS on the server. Also, we are not allowed to log messages to the server. And as a final straw, there is no scope for developement outside of DTS.
This probably answers you question.
Phill
--------------------
Colt 45 - the original point and click interface -
Sounds like a nightmare. I don't suppose you could install SQL on the NT box that's downloading the files?
Another question. Which will run more efficiently? Reading a constant that sets the step name, or looping through the package steps to find which step is executing? Which is less prone to failure, and easier to maintain? IMHO you should keep it simple.
Good luck,
John
-
quote:
Sounds like a nightmare. I don't suppose you could install SQL on the NT box that's downloading the files?Another question. Which will run more efficiently? Reading a constant that sets the step name, or looping through the package steps to find which step is executing? Which is less prone to failure, and easier to maintain? IMHO you should keep it simple.
Good luck,
John
Install SQL on the NT box, now thats a good one
We might have to get the project timeline extended for a few months whilst awaiting approval ... From an efficiency point of view, the constant would probably be more efficient than looping through the steps.
From an ease of maintenance point of view, having it loop through the steps makes it self-maintaining. If the package structure changes, you don't have to change anything in the code, it picks up the step in the loop.
All that being said, I'll probably go with setting the constant. I've found no way of definitively determining the current step name. Maybe one for the wish list
Phill
Edited by - phillcart on 08/26/2002 7:59:44 PM
--------------------
Colt 45 - the original point and click interface -
Wait for approval - now that's a good one! Ok, if not full blown SQL Server, how about the MSDE engine? Install Visio 2000 and you get it thrown in.
-
quote:
Wait for approval - now that's a good one! Ok, if not full blown SQL Server, how about the MSDE engine? Install Visio 2000 and you get it thrown in.Hmmm ... Visio is classed as a desktop application and can only be installed on desktops and therefore cannot be used with production server processes ... blah ... blah...
Sound familiar
All this may be for nothing anyway. The project has take a right turn and we are no going to use a stand-alone SQL Server
Now I can actually do some real work
--------------------
Colt 45 - the original point and click interface -
Hmmmmm Log to a desktop? Sounds feasable to me. These days the lines between workstation and server blur so easily.
Now that figures. Find an elegant solution to outragous constraints only to have the project simplified. Unfortunately, that does sound familiar.
Real work? They're sending you off to do manual labor? Say it aint so!
What part of Oz are you in Phil? I have some friends in Melbourne.
-
quote:
Hmmmmm Log to a desktop? Sounds feasable to me. These days the lines between workstation and server blur so easily.Now that figures. Find an elegant solution to outragous constraints only to have the project simplified. Unfortunately, that does sound familiar.
Real work? They're sending you off to do manual labor? Say it aint so!
What part of Oz are you in Phil? I have some friends in Melbourne.
I gave up manual labour years ago. We've just been documenting to death for the last month or so.I'm from Glen Iris which is sort of inner eastern suburbs of Melbourne.
--------------------
Colt 45 - the original point and click interface -
I had a similar problem where I was executing several DTS packages from within another DTS package and had a need to log each step/task with execution information (start time, end time, result, etc.). Below is some sample code that I put in an ActiveX Script to accomplish this task using a stored proc to write to a log table. The code changes the SQL statement of the stored proc to information pertaining to the particular task that was executed and when executed, writes a log record to the table.
Sample Code
Function Main()
Dim objPkg
Dim objLOGpkg
Dim objLOGtask
Dim myLogMsg
Dim iCount
'Initialize LOG package
Set objLOGpkg = CreateObject("DTS.Package")
objLOGpkg.LoadFromSQLServer "servername", "user", "password", DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Process_Log"
Set objLOGtask = objLOGpkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
'--------------------------------------------
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "servername", "user", "password", DTSSQLStgFlag_UseTrustedConnection,,,, _
"BKE_DTS_Create_New_Table"
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
myLogMsg = "Successful"
Else
myLogMsg = "Failed"
End If
myLOGtask.SQLStatement = "exec sp_myProcessTime " & _
"'" & objPkg.Name & "', " & _
"'" & objPkg.Steps(iCount).Description & "', " & _
"'" & objPkg.Steps(iCount).Name & "', " & _
"'" & myLogMsg & "', " & _
"'" & objPkg.Steps(iCount).StartTime & "', " & _
"'" & objPkg.Steps(iCount).FinishTime & "'"
myLOGpkg.Execute
Next
'Clean-up objects
Set objLOGtask = Nothing
Set objLOGpkg = Nothing
Set objPkg = Nothing
Main = DTSTaskExecResult_Success
End Function
.
We might have to get the project timeline extended for a few months whilst awaiting approval ... 
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply