SQL Server Performance

How to know the time taken by each process in DTS

Discussion in 'SQL Server DTS-Related Questions' started by vvkp, Aug 21, 2003.

  1. vvkp New Member

    Hi friends,
    I have one DTS package with four TASKS 1.Dropping indexes 2. Copydata 3.Creating indexes 4.Running a storedprocedure. I want to know how much time is taking for EACH TASK . Can anybody guide me how to do that?
  2. satya Moderator

    Use the DTS package log to troubleshoot problems or get information that occurred during the execution of a DTS package.

    You must enable package logging on a package-by-package basis. If package logging is enabled, the executing package writes information to the package log about all steps in the package, whether or not an individual step runs. If a step runs, it will retain start and end times, and the step execution time. For steps that do not run, the log lists the steps and notes that the step was not executed.

    (Gaurav, move it to DTS forum. Thanks)

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  3. gaurav_bindlish New Member

    You can create a table in the database and then log the time stamps into that by adding statements at beigining and end of the step.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
  4. Jon M Member

    Or you may create a text file and try below 'Execute SQL Task' script:<br /><br />--put this before each task<br />declare @vstart sysname<br />set @vstart = 'echo Time Start: ' + cast(getdate() as varchar(30)) + '&gt;&gt;c:mylog.txt'<br />exec master..xp_cmdshell @vstart, no_output<br /><br />--put this after each task<br />declare @vfinish sysname<br />set @vfinish = 'echo Time Finish: ' + cast(getdate() as varchar(30)) + '&gt;&gt;c:mylog.txt'<br />exec master..xp_cmdshell @vfinish, no_output<br /><br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />] Jon M
  5. satya Moderator

    Jon I appreciate your reply and it helped me other way.
    Thanks.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com

Share This Page