Where is DTS package logging ?

• Chaivat Jirapummin

  SSC Veteran

  Points: 274

  More actions

  November 16, 2006 at 5:45 am

  #72188

   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

  More actions

  November 16, 2006 at 11:33 am

  #672388

  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

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