How to interpret data that is logged by using a SQL Server 2005 Integration Services log provider

Microsoft SQL Server 2005 Integration Services (SSIS) uses the Execute Package task to support a parent-child package relationship. The Execute Package task is one of the available control flow objects in an SSIS project. You can use the Execute Package task to call another package as part of a work flow. An SSIS package can use an SSIS log provider to log event information. When a parent package executes, the SSIS log data is logged from two SSIS log providers, the child package and the parent package. This article discusses how to interpret data that is logged by using a SQL Server 2005 Integration Services log provider. The article also contains information to help you develop queries that are based on the logged data.

Note In this article, a called package is referred to as the "child" package. A calling package is referred to as the "parent" package.

When you execute a parent package and the child package has been designed to log entries by using an SSIS log provider, the entries are logged two times. The first time, the entries are logged under the execution context of the child package. The second time, the entries are logged under the execution context of the parent package. To identify the execution context, examine the executionID field in the log data. (The executionID field contains a unique GUID.) Log entries that are logged by a child package under the execution context of a parent package contain a User: prefix in the event field. For example, if the execution context is under a parent package, the event field contains User:OnPreExecute instead of OnPreExecute.

Example 1: Two packages logged under a single execution context

The following example shows a set of log entries that were logged by a parent package and a child package to an SSIS log provider for SQL Server. These entries were logged under the execution context of the parent package. This example includes two entries for the OnPreExecute event and two entries for the OnPostExecute event. The entries that are posted by the child package have a User: prefix in the event field. The executionID information is the same for all the entries that belong to a particular execution context.

The Log Events window makes it easy to see log events in SQL Server Business Intelligence Development Studio and to identify the executionID field and other fields. To open the Log Events window, click the Control Flow tab in SSIS Designer, and then click Log Events on the SSIS menu. If you use an SSIS log provider for SQL Server to log events, you can use the following sample Transact-SQL statement to retrieve log information for parent and child packages. An SSIS log provider always saves data to the sysdtslog90 table in a SQL Server database. Therefore, run the query against the sysdtslog90 table in the SQL Server database that you chose for the SSIS log provider.Note To use this sample, substitute executionID and source entries that apply to the specific log data.

Example 2: A single package logged under two execution contexts

The following example lists log entries that were logged by a child package under two execution contexts. This example includes the two child package log entries from the first example. The entries have the same sourceID information. However, the entries are distinguished by the User: prefix in the event field and by the different executionID entries. If you use a SSIS log provider for SQL Server to log events, you can use the following sample Transact-SQL statement to retrieve log information for child packages. Note This Transact-SQL statement uses a Top operator. Therefore, the Transact-SQL statement queries only the recent log data from the latest package execution.

For more information about how to retrieve and interpret SSIS log provider data, see the following topics in Microsoft SQL Server 2005 Books Online:

• "Execute Package Task"
• "Integration Services Log Providers"
• "Implementing Logging in Packages"