Microsoft KB Archive/906563

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

Article ID: 906563

Article Last Modified on 3/11/2006

-

APPLIES TO


 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard Edition

-





INTRODUCTION
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.



MORE INFORMATION
Note In this article, a called package is referred to as the &quot;child&quot; package. A calling package is referred to as the &quot;parent&quot; 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. source                 sourceID                                 event                executionID

=
============================================================================================================== AdventureWorks_parent  72CF88F8-952D-4D5D-B67F-D52AE1690E0B     PackageStart        161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_parent  72CF88F8-952D-4D5D-B67F-D52AE1690E0B     OnPreExecute        161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_child   472C4826-4278-4D29-9CBA-6586AC7FE418     User:OnPreExecute   161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_child   472C4826-4278-4D29-9CBA-6586AC7FE418     User:OnPostExecute  161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_parent  72CF88F8-952D-4D5D-B67F-D52AE1690E0B     OnPostExecute       161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_parent  72CF88F8-952D-4D5D-B67F-D52AE1690E0B     PackageEnd          161B2818-8BB3-40A6-9C1F-5DC4F7225556 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. SELECT source,sourceID,event,executionID from sysdtslog90 where event in ('onpreexecute','onpostexecute','user:onpreexecute','user:onpostexecute','PackageStart','PackageEnd') and [executionid] like ('161B2818-8BB3-40A6-9C1F-5DC4F7225556%') and source in ('AdventureWorks_parent','AdventureWorks_child') order by starttime 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. source                sourceID                                  event                executionID

=
============================================================================================================== AdventureWorks_child  472C4826-4278-4D29-9CBA-6586AC7FE418      User:OnPostExecute   161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_child  472C4826-4278-4D29-9CBA-6586AC7FE418      OnPostExecute        8C43E096-63DF-4692-98E4-49E88D271734 AdventureWorks_child  472C4826-4278-4D29-9CBA-6586AC7FE418      User:OnPreExecute    161B2818-8BB3-40A6-9C1F-5DC4F7225556 AdventureWorks_child  472C4826-4278-4D29-9CBA-6586AC7FE418      OnPreExecute         8C43E096-63DF-4692-98E4-49E88D271734 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. SELECT top (4) source,sourceID,event,executionID from sysdtslog90 where event in ('onpreexecute','onpostexecute','user:onpreexecute','user:onpostexecute') and Source in ('AdventureWorks_parent','AdventureWorks_child') and sourceid='472C4826-4278-4D29-9CBA-6586AC7FE418' order by starttime desc 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.

