PackageTaskLog Problem

bartos · November 12th, 2010, 01:15 PM

I've setup the framework and I am seeing an issue with the PackageTaskLog table. I have setup an SSIS package that uses the template and calls a child package which also uses the template. Now in the PackageTaskLog table I have several entries for SQL LogTaskPreExecute and SQL LogTaskPostExecute...I don't think those should be showing up in there should they?

Am I missing something? Thanks in advance.

bartos · November 12th, 2010, 05:06 PM

After considering this some more and doing some additional research (thanks google!), it seems that the proper approach to this scenario would be to have a different template for packages that are going to be called from a parent package. That way there are no event bubbling issues, basically what I've done to work around this is create a new template specifically for child packages that doesn't have any event handlers, because the child packages will always be called from a parent and the parent will inherit from the template so the logging will be handled there.

Hopefully this will help someone who comes across this issue.

jjvinuales · November 25th, 2010, 01:13 PM

Hi Bartos.

I'm facing the same problem than you. Your approach is good but you are loosing the context of the package raising the event. Every record in appears belonging to the PackageLogId of the parent package and you donÂ´t know the real package which raised the event.

What do you think?

bartos · November 28th, 2010, 12:48 AM

Yes, you are correct the exact context of the package raising the event is lost. In my case that is something that I'm willing to live with. If you figure out an alternative solution, please post it here. I'd love to see what you come up with.

If you think that I can help, feel free to reach out to me if you want to bounce any ideas off of me.

jjvinuales · November 28th, 2010, 12:42 PM

Hi Bartos.

I have got a solution valid for my project. Hope it helps you.

Our problem was that the events raised in a child package, are propagated to the parent package always (useless try to use the system variable Propagate=False).

The first container receiving that event in the parent package is the Execute Package task. So you can put a dummy event in that container and set Propagate=True in that event.

For example, OnPreExecute. I put an OnPreExecute event in the Execute Package task with an empty Script Task (nothing to do). So when the inner event comes to the parent package, that dummy task in the OnPreExecute event of the ExecutePackage task is executed. You have controlled the propagation of this events.

You can mantain the framework structure but you must be aware that everytime you put an execute Package task in the workflow and you have events in the child package that you donÂ´t want propagate to the parent package you must put dummy tasks for those events in the Execute Package Task.

Grettings from spain.

bartos · December 3rd, 2010, 05:31 PM

Very nice solution. It's too bad that the "Propogate" properties don't actually work like we expect them too. Thanks for the update on your solution.

BarneyL · August 23rd, 2012, 03:54 AM

This is an old thread but I came accross the same issue with the framework and thought I'd log my solution here in case it helps anyone else.
Below is my revised code for the LogTaskPreExecute stored procedure.
We've put all our logging tables and stored procedures in a schema named logging so others will need to convert this back to wherever their stuff is stored.
What the changes do:
1) Explicitly excludes the tasks 'SQL LogTaskPreExecute' and 'SQL LogTaskPostExecute' so the child package's use of these doesn't appear in the parent package's task log
2) When a task is started the procedure checks to see if any other package in the batch created after the current package (i.e. a child package) has logged a task with the same source ID in the last half second. If there is then it doesn't log the task.
This works for us although comes with the warning that potentially it could fail to log valid tasks in some circumstances (e.g. if the parent package needed to call the same child package twice at the same time for some reason).

Code:

CREATE PROCEDURE [logging].[LogTaskPreExecute]
(    @PackageLogID int
    ,@SourceName varchar(64)
    ,@SourceID uniqueidentifier
    ,@PackageID uniqueidentifier
)

AS
BEGIN
    SET NOCOUNT ON
    IF @PackageID <> @SourceID
        AND @SourceName <> 'SQL LogPackageStart'
        AND @SourceName <> 'SQL LogPackageEnd'
        AND @SourceName <> 'SQL LogTaskPreExecute'
        AND @SourceName <> 'SQL LogTaskPostExecute'
        AND NOT EXISTS
(SELECT 1
FROM
	logging.PackageTaskLog
WHERE
	SourceID = @SourceID
	AND StartDateTime > dateadd(millisecond,-500,getdate())
	AND PackageLogID IN (SELECT PackageLogID
					 FROM
						 logging.PackageLog
					 WHERE
						 BatchLogID = (SELECT BatchLogID
									   FROM
										   logging.PackageLog
									   WHERE
										   PackageLogID = @PackageLogID)
						 AND PackageLogID > @PackageLogID)
)					
        INSERT INTO logging.PackageTaskLog (PackageLogID, SourceName, SourceID, StartDateTime)
        VALUES (@PackageLogID, @SourceName, @SourceID, getdate())
END