Logging

Just when you were truly amazed at all of the control flow, event-handling, and debugging features, you find out about Package Logging. Logging enables you to record information about events you are interested in as the package runs. The logging information can be stored in a text or XML file, to a SQL Server table, to the Windows Event Log, or to a file suitable for Profiler.

Logging can be enabled for all or some tasks and containers and for all or any events. Tasks and containers can inherit the settings from parent containers. Multiple logs can be set up, and a task or event can log to any or all logs configured. You also have the ability to control which pieces of information are recorded for any event.

Open one of the packages you created earlier in this chapter or any package with several Control Flow tasks. From the menu, navigate to SSIS Logging to open the Configure SSIS Logs dialog box. To enable logging, you must first check the box next to Package in the left pane (see Figure 13-23).

Figure 13-23

Notice that the checkboxes for the child objects in the package are grayed out. This means that they will inherit the logging properties of the package. You can click into any checkbox to uncheck an object. Clicking again to check the box will allow you to set up logging properties specific for that task or container.

To get started, the log providers must be defined at the package level. Select Package in the TreeView control on the left. Choose which type of provider you would like to configure; in this case, choose SSIS Log Provider for XML File. Click Add to add the provider to the list. Click the drop-down under Configuration and choose <New Connection>. The File Connection Manager Editor opens. Set the Usage Type property to Create File. Type the path for the XML file or click Browse to the XML file location as in Figure 13-24.

Figure 13-24

Click OK to accept the configuration and dismiss the dialog box. In the Configure SSIS Logs dialog box, you should now see the new log provider and its properties. Check the box next to the new logging provider to enable it at the package level. At this point, you can give the log provider a descriptive name if you wish as in Figure 13-25.

Figure 13-25

Click the Details tab to view a list of events that you can log. By clicking Advanced, you will also see a list of possible fields (see Figure 13-26). Choose one or two of the events. Notice that all of the fields are automatically chosen. You can uncheck some of the fields if you don't think the information will be useful.

Figure 13-26

Move back to the Providers and Logs tab. When you checked the log provider as Package was selected on the left, you enabled that log for all components in the package that are set to inherit settings from their parent container. Even if that log provider is chosen for an object that does not inherit the log settings, you can use it to select different events and fields (see Figure 13-27).

Figure 13-27

Once you modify the logging on a parent container, such as a For Loop container, the child objects will now inherit from the container, not the package.

When you are satisfied with the logging settings, click OK to close the dialog box. If you view the Properties window of a task or container, you will find the LoggingMode property. This property can be set to UseParentSetting, Enabled, or Disabled and will match the settings you just configured.

Run the package. Once the package execution has completed, open the log file to view the XML (see Figure 13-28).

Figure 13-28

Setting up logging for a package can be as complicated or as simple as you would like. It's possible that you may want to log similar information, such as the OnError event, for all packages. You can save the settings as a template by clicking Save when on the Detail tab of the Configure SSIS Logs dialog box. Alternately, you can load a previously saved template by clicking the Load button.