Logging DTS execution times
-
I need a way to write the start time, table names for source and destination tables and estimated record count before the execution of a dts package and update this information at the completion of the package with the ending time. The solution needs to be easy to use and as automatic as possible. Do you have any ideas or can you recommend any resources that will help me with this? Thanks.
-
How dynamic is your package? ie: do you assign items like the source and destination tables from global variables at run-time, or a they statically assigned when the package is built?
If the former, then for simplicity in the DTS package, write a stored procedure that logs the information required passing it your global variables.
If the latter, then you'll need to write a bit of ActiveX script to retrieve the relevant information before writing it the database.
Either way, all you'd really add to your DTS package is two extra tasks. One at the start and one at the end.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
You can loop through the steps of the DTS and execute a stored proc that logs the information into a "Log table".
I use the following code to accomplish a similar task. All you would need to do is add code to count the records before executing a query and after it has been executed.
Code Snippet
'Create and Execute the package
Set objPkg = CreateObject("DTS.Package")
objPkg.LoadFromSQLServer "servername",,,DTSSQLStgFlag_UseTrustedConnection,,,,"dts_package_name"
objPkg.Execute
'Check For Errors
For iCount = 1 To objPkg.Steps.Count
If objPkg.Steps(iCount).ExecutionResult = 0 Then
my_Log_Msg = "Successful"
Else
my_Log_Msg = "Failed"
End If
my_LOG_task.SQLStatement = "exec usp_myProcessTime " & _
"'" & 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
-
Thanks to both of you for your help. I need to get the rest of the details on the project, but now I understand how to approach this.
-
Another good resource for DTS besides this site (which is the best!!!) is http://www.sqldts.com/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply