HOW TO: Log Data Transformation Services Package Execution to a Text File

SQL Server can log information about package execution and the detailed step level execution in a package to a text file. You can use the text log file to troubleshoot both package level and step level errors. This article describes how you enable logging to a text file.

SQL Server 2000 Steps to Enable Logging to a Text File

1. Open a package in the Data Transformation Services (DTS) Designer in Enterprise Manager.
2. On Package menu, click Properties.
3. In the Properties dialog box, click the Logging tab.
4. In the Error Handling section, type a file name in the Error file text box. For example, C:\Errordts.log. You can also click the Browse button and browse to the folder in which you want to write the log file. You can also browse to an existing error log file and have the error log append to that file.
5. Select the Fail package on first error option if you want the whole package to fail if any of the steps fail for the first time.

SQL Server 7.0 Steps to Enable Logging to a Text File

1. Open a package in the DTS Designer in Enterprise Manager.
2. On the Package menu, click Properties.
3. In the Properties dialog box, click the Global tab.
4. In the Error Handling section, type a file name in the Error file text box. For example, C:\Errordts.log. You can also click the Browse button and browse to the folder in which you want to write the log file. You can also browse to an existing error log file and have the error log append to that file.
5. Select the Fail package on first error option if you want the whole package to fail if any of the steps fail for the first time.

Troubleshooting

The path specified for the error log file must exist and the proper permissions to the folder, the drive, and path must be set to make sure that the error log file is written. The permissions you must have are dependent on where the DTS package executes. For additional information about permissions related to DTS packages, click the article number below to view the article in the Microsoft Knowledge Base: The error log file is appended to each time the DTS package runs. The error file will include each step even if the step is not run. This is different from logging to SQL Server, which does not insert a row into the sysdtssteplog system table if the step does not run. You can use the error log to learn which step stopped responding and why it stopped responding.

SQL Server Books Online; topics: "DTS Package Properties (Logging Tab)"; "Using DTS Package Logs"