Log DTS package to a SQL Server Table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log DTS package to a SQL Server Table

I am trying to log DTS package with steps/tasks info into a SQL Server table. I need the following info:
Package name, step description, step status, step starttime, step finish time, step executiontime. I am using step properties to get the info, but not getting right info. Thanks, Alim

How about to use Profiler?
You can save into SQL table. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
DTS package execution is already logged in msdb database, I think.
Thank you anyway. I got it. Please see the following script. It can log any number of steps/tasks info of a DTS pacakge into a SQL server Table. Thanks, Alim Mia ‘**********************************************************************
‘ Visual Basic ActiveX Script
‘************************************************************************
Function Main()
dim myConn
dim myRecordset
dim mySQL
dim oPackage
dim i ‘ assign current dts package
set oPackage = DTSGlobalVariables.Parent set myConn = CreateObject("ADODB.Connection")
set myRecordset = CreateObject("ADODB.Recordset") ‘ set the connection properties to point to the MEIS database
myConn.Open = "Provider=SQLOLEDB.1;Data Source=XXXXX; Integrated Security=SSPI" ‘ open DTS_Log table
mySQL = "select * from dbo.Log"
‘ mySQL = "dbo.DTS_Log"
myRecordset.Open mySQL, myConn, adOpenKeySet , 2 , adCmdTable ‘ Loop through all steps/tasks and log the steps info to DTS_Table ‘ For each otask in opackage.steps
For i = 1 to oPackage.steps.count
If oPackage.Steps(i).ExecutionStatus = 4 Then
MyRecordset.AddNew
MyRecordset.Fields("PackageName") = opackage.name
If oPackage.Steps(i).ExecutionResult = 0 Then
MyRecordset.Fields("Status") = "Success"
Else
MyRecordset.Fields("Status") = "Failed"
End if
MyRecordset.Fields("TaskDescription") = oPackage.Steps(i).Description
MyRecordset.Fields("StartDateTime") = oPackage.Steps(i).starttime
MyRecordset.Fields("EndDateTime") = oPackage.Steps(i).Finishtime
MYRecordSet.Fields("Duration_in_Minutes") = oPackage.Steps(i).ExecutionTime/60
MyRecordset.Update
End if
Next Set MyRecordset = Nothing
Set myconn = Nothing
‘ myConn.Close
‘ MyRecordset.Close Main = DTSTaskExecResult_Success
End Function

Books online has got this information to setup logging fro a DTS package:

How to Enable Package Logging (Enterprise Manager)
New Information – SQL Server 2000 SP3. To enable package logging Open the Data Transformation Services (DTS) package for which you want to create a log.
On the Package menu, click Properties to display the DTS Package Properties dialog box.
Do one of the following:
Save package logs to Microsoft® SQL Server™ by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.
Security Note When possible, use Windows Authentication. Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS Package dialog box, in the Location list, select Meta Data Services.
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>