Implementing Package Logging | MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step

The following procedures will teach you how to log events as a package executes.

Configuring Package Logging

The first step in implementing package logging is to configure logging for the package. In this procedure, you will use the SSIS Log Provider for SQL Server to configure the package.

Configure Package Logging

In BIDS, in Solution Explorer, double-click NewProducts.dtsx to open the package designer.
On the SSIS menu, click Logging. The Configure SSIS Logs: NewProducts dialog box opens.
In the Configure SSIS Logs: NewProducts dialog box, in the Containers pane, select the NewProducts check box.
Expand all the nodes of the tree, and then clear all the check boxes.
Click NewProducts.
On the Providers And Logs tab, in the Provider Type drop-down list, verify that SSIS Log Provider For Text Files is selected, and then click Add. In the Select The Logs To Use For The Container list, a new entry is added to the table.
Select the new entry check box and rename it PackageTextLog.
Click the field in the Configuration column, and then select <New Connection…>.

The File Connection Manager Editor opens.

Your screen should look similar to this:
In the File Connection Manager Editor dialog box, in the Usage Type drop-down list, select Create File.
In the File text box, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Logs\PackageText.log.
Click OK to close the editor.
In the Configure SSIS Logs: NewProducts dialog box, in the Provider Type drop-down list, select SSIS Log Provider For SQL Server, and then click Add. In the Select The Logs To Use For The Container list, a new entry is added to the table.
Select the new entry check box and rename it PackageSQLLog.
Click the field in the Configuration column, and then select LocalHost.QuickStartODS.

Note
Log entries are written to the sysdtslog90 table. This table is created the first time the package is executed with logging enabled.
On the Details tab, select the OnError, OnPreExecute, and OnTaskFailed check boxes.
Click OK to close the Configure SSIS Logs: NewProducts dialog box.

Logging is now configured for the package. No log entries will be created for the containers or tasks.

Note
An object can write log entries to multiple log providers, but the same entries will be written to all of the log providers.

Executing the Package and Viewing the Logs

Now that you have configured the package for logging, you are ready to execute the package and view the logs. In the following procedures, you will execute the NewProducts.dtsx package and view its log entries. You will then configure container and task logging for the NewProducts.dtsx package and then again execute the package and view its log entries.

Execute the Package and View the Logs

In BIDS, right-click NewProducts.dtsx, and then click Execute Package. Wait until the package has executed successfully.
In Windows Explorer, open the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Logs\PackageText.log file and view the log entries.
In SQL Server Management Studio, in the left pane, expand Databases and QuickStartODS. Expand the Tables folder, and then right-click dbo.sysdtslog90 and click Open table. View the log entries.
In BIDS, on the Debug menu, click Stop Debugging.

Configure Container and Task Logging

In BIDS, in Solution Explorer, double-click NewProducts.dtsx.
On the SSIS menu, click Logging. The Configure SSIS Logs: NewProducts dialog box opens.
In the Configure SSIS Logs: NewProducts dialog box, on the Providers And Logs tab, in the Provider Type drop-down list, verify that SSIS Log Provider For Text Files is selected, and then click Add.
In the Select The Logs To Use For The Container list, a new entry is added to the table.
Rename the new entry ClearMatchedNamesLog and ensure that the new entry is not selected.
Click the field in the Configuration column, and then select <New connection…>. The File Connection Manager Editor opens.
In the Usage Type drop-down list, select Create File.
In the File text box, navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Logs\ClearMatchedNames.log.
Click OK to close the editor.
In BIDS, in Solution Explorer, double-click NewProducts.dtsx to open the project designer.
On the SSIS menu, click Logging. The Configure SSIS Logs: NewProducts dialog box opens.
In the Configure SSIS Logs: NewProducts dialog box, in the Containers pane, expand all the nodes of the tree.
Select the Script Task - Check Products List check box twice.

When the Script Task - Check Products List check box is selected twice, the check mark should be grayed (dimmed). This means that the Script Task - Check Products List task will inherit its logging settings from its parent (NewProducts package). Notice that you cannot change the content of the Providers and Logs tab or the Details tab.
Select the Execute SQL Task - Clear Matched Names check box.

A black check mark means the object has its own log settings.
On the Providers And Logs tab, select the ClearMatchedNamesLog and the PackageSQLLog check boxes. (The PackageTextLog check box should be cleared.)
On the Details tab, select the Events check box.

All Execute SQL Task - Clear Matched Names events will be logged.
Click OK to close the Configure SSIS Logs: NewProducts dialog box.

Execute the Package and View the Logs

In BIDS, in Solution Explorer, right-click NewProducts.dtsx, and then click Execute Package. Wait until the package has executed successfully.
In Windows Explorer, open the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Logs\PackageText.log file and view the log entries.
Open the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap08\Logs\ClearMatchedNames.log file and view the log entries.
Open the QuickStartODS database sysdtslog90 table and view the log entries.