SQL Server Performance

Log DTS package to a SQL Server Table

Discussion in 'SQL Server DTS-Related Questions' started by alimmia, Nov 8, 2005.

  1. alimmia New Member

    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
  2. Luis Martin Moderator

    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.



  3. mmarovic Active Member

    DTS package execution is already logged in msdb database, I think.
  4. alimmia New Member

    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
  5. satya Moderator

    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.

Share This Page