Where is DTS package logging ?

  • Chaivat Jirapummin

    SSC Enthusiast

    Points: 164

     Dear all,

    I am using SQL Server 2000 and specify DTS package logging by.

    1. Open a DTS package.

    2. Package -> Properties. Select tab logging. Check for “Log package execution to SQL Server” Then, specify correct user and password. I also check “Fail package on Log error'”

    3. In Error handling, I specify the full path of error file on client machine and check “Fail package on Log error” and “Write completion status to event log”

    I cannot find the error log. Where is DTS package logging ?

    Is it the same as database log ? Does it contain execution time in each node?

    Best regards,

    CJira

  • Erik Kutzler

    SSCarpal Tunnel

    Points: 4287

    The error log will be in the path you specified in the “Error file:” text box.  Are you using a drive letter to specify the path or are you using a UNC path?  If you’re using a drive letter in the path, make sure it exists on the machine the DTS package is being executed from.  (This means if the path is “h:\logs\Dtslog.txt” and you’re executing the package from your machinge, that path better be valid on your machine.  If the package is scheduled as a sql job, that path must exist on the sql server.)

    The error log does contain execution time.

    Checking the “Log package execution to SQL Server” will log information in the msdb.dbo.sysdtspackagelog table.

     

    SELECT * FROM msdb.dbo.sysdtspackagelog

    WHERE [name] = ‘name of the dts package’

     

    Here’s an example of what the error log file contains:

    ———————————————————

    The execution of the following DTS Package failed:

    Error Source: Microsoft Data Transformation Services (DTS) Package

    Error Descriptionackage failed because Step ‘DTSStep_DTSSendMailTask_1’ failed.

    Error code: 80040428

    \Error Help File:sqldts80.hlp

    Error Help Context ID:700

    Package Name: SendMailTest

    Package Description: (null)

    Package ID: {4510F542-1C37-4A4B-A4E8-5F407DBDF0E9}

    Package Version: {8616C9A6-0C07-4D80-9172-7B5C70BC5C77}

    Package Execution Lineage: {E2A0D0DE-D88B-45FF-B01E-15DF3190CE18}

    Executed On: MyLocalMachine

    Executed By: erik

    Execution Started: 11/16/2006 10:10:02 AM

    Execution Completed: 11/16/2006 10:10:03 AM

    Total Execution Time: 0.641 seconds

    Package Steps execution information:

    Step ‘DTSStep_DTSSendMailTask_1’ failed

    Step Error Source: Microsoft Data Transformation Services (DTS) Package

    Step Error Description:Logon failed: Unable to get the default MAPI Message Store due to MAPI error 285: You do not have permission to log on.

    Step Error code: 80040480

    Step Error Help File:sqldts80.hlp

    Step Error Help Context ID:9100

    Step Execution Started: 11/16/2006 10:10:02 AM

    Step Execution Completed: 11/16/2006 10:10:02 AM

    Total Step Execution Time: 0.578 seconds

    Progress count in Step: 0

    ****************************************************************************************************

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply