Enabling Package Logging

Logging of DTS package activity, by default, is not enabled. Logging records the time a package executes and the success or failure of each step. You can use DTS Designer to enable logging of all executions of a package to the msdb database in SQL Server. You can use the /L parameter in DTSRun to enable logging of an execution of a package to a text file. Because package logging incurs some performance overhead, to maximize performance in the production environment, you can choose to enable logging of package activity only during the development phase.

In addition to the logging of package activity, you can also enable the creation of an error file at execution time that records the status and error information for each step in a package. For information on enabling and configuring exception files to capture the actual data causing package or task failures, see Chapter 5. For information on adding error handling routines into your data movement application, see Chapter 8.

Use DTSRun to enable logging of package execution to a text file

In this procedure, you will learn how to enable package logging to a text file. You will use the UpdateTimeDim.cmd batch file, add the /L parameter, add an additional month of time values to the TimeDim table, and view the package execution log.

1. Switch to the UpdateTimeDim.cmd file in Notepad.

2. Add the following parameter and value to the UpdateTimeDim.cmd batch file (as a single line ”do not add a carriage return):

    /L"C:\MicrosoftPress\SQLDTSSBS\DataMovementApplication\LoadHistoricalDataExecutionLog.txt" 

3. Change the StartDate parameter in this batch file to 3/1/1998 , change the EndDate parameter to 4/1/1998 , and then save the UpdateTimeDim.cmd batch file.

   

   Tip  

   If you do not save the changes that you make to this file, you will re- enter the time members for January and February of 1998. This duplicate data will cause the Sales cube to fail when you get to Chapter 9.

4. Switch to the command prompt window, press F3 to retrieve the last command you executed (the UpdateTimeDim.cmd command), and press Enter to execute this command.

5. Close the command prompt window.

6. Switch to SQL Query Analyzer and re-execute the query in the query pane.

   The results pane displays the time members for the first three months of 1998.

7. Close SQL Query Analyzer without saving the changes to the script and then switch to Notepad.

8. On the File menu, click Open.

   The focus of Notepad should be C:\Microsoft Press\SQL DTS SBS\DataMovementApplication.

9. Double-click LoadHistoricalDataExecutionLog.txt to review the package execution log created in the file system.

   

   Details about the package execution along with details about the execution of each step are recorded. This log is very short because the PopulateTimeDimension package contains only a single step, named DTSStep_DTSExecuteSQLTask_1 . Do not close Notepad.

Now you are ready to learn how to enable a package to log its execution details to SQL Server.

Enable package logging and error handling in DTS Designer

In this procedure, you will learn how to enable package logging to SQL Server and create an error file. You will use the LoadHistoricalData package that you saved as a structured storage file to enable you to see a more substantial error log. (The LoadHistoricalData package contains many more steps than the PopulateTimeDimension package.)

1. Switch to SQL Server Enterprise Manager.

2. In the SQL Server Enterprise Manager console tree, right-click Data Transformation Services and then click Open Package.

3. Navigate to C:\Microsoft Press\SQL DTS SBS\DataMovementApplication in the Look In list, and then double-click LoadHistoricalData.dts.

4. Double-click LoadHistoricalData, type mypassword in the Password box, and then click OK.

5. On the Package menu, click Properties, and then click the Logging tab.

   

6. Select the Log Package Execution To SQL Server check box, type C:\Microsoft Press\SQL DTS SBS\DataMovementApplication\LoadHistoricalDataErrorLog.txt in the Error File text box, and then click OK.

   You can also choose to have the package fail if any step fails. The default package behavior allows a package to continue if a step fails, which is generally desired unless you are using transactions (see Chapter 5). This default behavior enables you to use On Failure constraints to define the action you want to occur when a step fails. (Chapter 8 discusses the use of error handling routines.)

   Tip  

   If errors are logged to an existing text file, error information is appended to the existing file.

7. On the toolbar, click Save.

8. Click Execute on the Package menu.

9. Click OK, and then click Done.

10. Close the LoadHistoricalData package in DTS Designer.

Review SQL Server package logs and error files

In this procedure, you will review the LoadHistoricalData package log in SQL Server, and then review the LoadHistoricalDataErrorLog.txt error file.

1. In the SQL Server console tree, expand Data Transformation Services, right-click Local Packages, and then click Package Logs.

   Each package that has logged its execution details to this SQL Server instance appears, regardless of where the package was stored.

   

2. Expand the log tree and then double-click Log 1 to display each step of the LoadHistoricalData package, along with step execution details.

   Notice that the step names are somewhat cryptic. In Chapter 7, you will learn about changing step names to more user -friendly names .

   

   Tip  

   To view details about a step that failed, click the step, or click the step and then click More Info >>>.

3. Click Close and then click Close again.

4. Close SQL Server Enterprise Manager.

5. Switch to Notepad.

6. On the File menu, click Open, and then double-click LoadHistoricalDataErrorLog.txt.

   Notice the step execution details recorded in this text file.

   

   Tip  

   Package error files record execution details for all steps in a package, whereas the package execution log records only information about steps that were actually executed. In Chapter 7, you will learn to use ActiveX Script tasks to add branching to the packages of the data movement application. With branching, the package error log can be useful for creating a log that keeps an audit trail of which steps executed each time a package was executed.

7. Close Notepad.

You have now learned about how to enable and use SQL Server and text file package execution logs and how to record error logs in the file system.