DTS Package monitoring.
-
I was wondering if anyone has a query built that can dig into the dts log table(s) to pull out step name and run times.
I do NOT want to use the system assigned stepname from sysdtssteplog, but rather the step Description that you enter.
Alternatively, if anyone knows what table the step description is stored in, that would be awesome as well.
I can't find the damn "step description" in any of the tables in msdb.
tia,
Bob
-
Unfortunately the DTS packages, including all the steps are contained in a binary image in sysdtspackages so there is no way of easily retrieving the step descriptions. However, you can change the step names using the Disconnected Edit and then the step names in sysdtssteplog will actually mean something to you.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Very Cool Trick.
I just tested it out though, and linked steps won't execute after the first.
Is there a certain "key" that needs to be changed? I tried the tasks Name key and the steps Name key.
I also reconnected the workflow AFTER I had changed the stepname.
Very simple package, an ole db connection and two sql tasks against northwind. Select * from sysobjects
-
I'm not sure what you mean by Linked Steps. You do need to redo any workflow definitions that were in place before you rename the steps, but I've not had any issues with steps not executing.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I tried redoing the workflow. Tried opening and closing the packages. tried deleting the workflow, then changing the properties, then adding back in the workflow. No dice for me. It executes the first step,then gives me a big red x for the second step.
If I check the log, it doesn't even show the 2nd step as being executed.
Just wondering which data item you have changed. I tried Steps \ name and Tasks\name
My SQL at home might be out of date, so I'll have to try it once I get in to work tomorrow.
-
If you are getting a red x for the second step, the step was attempted and errrored out. Did you get any kind of error message when you double click that line in the results? Can you execute the second step by itself?
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Yup, I can execute the second step from inside the package. ( Its only a "select * from sysobjects, just like the first step. It is actually a cut and paste of the first step. maybe that is affecting it?)
If I execute the package itself though, either inside or outside, I get the red x for step 2, but cannot get any detail information. If I go into the package log, there *IS NO* second step.
Bah, let me check tomorrow at work. My home copy of sql 2k might still be at sp1 or 2. It doesn't get used too very much, so might be out of date.
Thanks for the info though!!
-
You can execute a DTS package from within an ActiveX Script and then log what the results were of each step (log meaning write to a table). Then you can check the log table for what happened. The information in red below is the name that you give each task when building the DTS that you want to execute (I believe what you're looking for). The information in blue below is the name that SQL Server gives to each task, like 'DTSStep_DTSExecuteSQLTask_5' or whatever you have in the DTS (what you don't want to see). This procedure below does need an additional DTS that performs the logging and it simply is an 'ExecuteSQL' task for which you will change the SQL string and then execute to write the results of each step to the logging table (this is the loop shown below).
Hope this makes sense.
Enjoy,
Brian
Function Main()
'Declare variables
Dim objPkg
Dim iCount
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' B E G I N L O G P R O C E S S . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Declare variables
Dim my_LOG_pkg
Dim my_LOG_task
Dim my_Log_Msg
Dim my_Log_flag
'Initialize LOG package
Set my_LOG_pkg = CreateObject("DTS.Package")
my_LOG_pkg.LoadFromSQLServer "yourServerName", , , DTSSQLStgFlag_UseTrustedConnection,,,, _
"yourLoggingDTSPackageName"
Set my_LOG_task = my_LOG_pkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' B E G I N L O G P R O C E S S . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------
' S T A R T E X E C U T I N G D T S P A C K A G E
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer " yourServerName ", , , DTSSQLStgFlag_UseTrustedConnection,,,, _
"yourDTSPackageNameToBeLogged"
objPkg.Execute
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' L O G P R O C E S S . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Check For Errors
my_Log_flag = "Successful thus Continue DTS"
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
my_Log_Msg = "Successful"
Else
my_Log_Msg = "Failed"
my_Log_flag = "Failed thus Stop DTS"
End If
my_LOG_task.SQLStatement = "exec usp_ProcessTime " & _
"'" & objPkg.Name & "', " & _
"'" & objPkg.Steps(iCount).Description & "', " & _
"'" & objPkg.Steps(iCount).Name & "', " & _
"'" & my_Log_Msg & "', " & _
"'" & objPkg.Steps(iCount).StartTime & "', " & _
"'" & objPkg.Steps(iCount).FinishTime & "'"
my_LOG_pkg.Execute
Next
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Set objPkg = Nothing
If my_Log_flag = "Failed thus Stop DTS" Then
Set my_LOG_task = Nothing
Set my_LOG_pkg = Nothing
Main = DTSTaskExecResult_Failure
Exit Function
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' E N D L O G P R O C E S S . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Clean-up objects
Set my_LOG_task = Nothing
Set my_LOG_pkg = Nothing
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' E N D L O G P R O C E S S . . .
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Main = DTSTaskExecResult_Success
End Function
-
Why not use Package Logging? Granted, it takes up spacce, but I think you can get what you need from there (ie troubleshooting why a package failed).
-
Yes, package logging is nice for troubleshooting. I normally add this to the DTS package as well as the job (if scheduled). This will provide start and end times as well as execution status (successful vs failed) for each task in the DTS, but it will not have the user-defined name for the task (the description), it will only show what SQL Server labels the task (like 'DTSStep_DTSExecuteSQLTask_5').
The description is what is desired in the original post, but as you stated, package logging is very nice when it comes to debugging.
-
I use the disconnected edit option but the catch is you need to change several properties per task and step. If I have mulitple processes/tasks in a package I perform the disconnected edit after adding each piece (just makes it easier to keep track of what I am doing :blink
. Under the Tasks - select one and copy the Description to the Name field, then find the matching item under Steps and copy the description to the Name and TaskName fields. Now if you turn on package logging you will see your descriptive field as the task name along with all the other stats. -
Nice Tip -- This would be very useful in production processes and make it very easy to track down which specific task failed.
-
DSP,
AAAHHHhhh. I was only changing them in one spot. It also seems that you should delete the workflow before renaming, otherwise you also need to change precedence constraints under steps.
It is working great now. Thanks for all the help folks!
-
Bob,
Yep - you have two choices at the precedence, delete and redo or copy from the step(s) name. I prefer the redo (it is easier!!)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply