MORE INFORMATION
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.
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.