Wrox Programmer Forums
|
BOOK: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution
This is the forum to discuss the Wrox book Microsoft SQL Server 2008 Integration Services Problem-Design-Solution by Erik Veerman, Jessica M. Moss, Brian Knight, Jay Hackney ; ISBN: 978-0-470-52576-0
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Microsoft SQL Server 2008 Integration Services Problem-Design-Solution section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old November 12th, 2010, 01:15 PM
Registered User
 
Join Date: Nov 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default PackageTaskLog Problem

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.
 
Old November 12th, 2010, 05:06 PM
Registered User
 
Join Date: Nov 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 25th, 2010, 01:13 PM
Registered User
 
Join Date: Nov 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?
 
Old November 28th, 2010, 12:48 AM
Registered User
 
Join Date: Nov 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old November 28th, 2010, 12:42 PM
Registered User
 
Join Date: Nov 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default My solution

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.
 
Old December 3rd, 2010, 05:31 PM
Registered User
 
Join Date: Nov 2010
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old August 23rd, 2012, 03:54 AM
Registered User
 
Join Date: Aug 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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









Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.