Finding Containers and Variables from Script task?

Hi,
* Here's my use case:
I have 20 odd event handlers in various containers that all do the exact same thing. Even though they're simple (a Script and an Execute SQL Task), its a right pain to copy them & keep them synced. I could probably create a custom task for the content of each handler, but I would still need to copy & configure it across 20 event handlers.
My (maybe impossible) idea to handle this was to:
Create a single event handler at the global scope. When invoked, use System::SourceID or System::SourceName to either
- GetContainer(SourceID).Variables("myVar") (had there been such a function call)
- Traverse the package object model to find the container, i.e:
Package.Executables(SourceName).Variables("myVar")
Is it true that there is no practical way of doing this?

* "Variables on the container ... are visible to the event handler that handles the event on that container"
As I understand it, this is only true when attaching an event handler to the specific container generating the event. If a container has no event handler, the event will propagate up through the hierarchy all the way to the package scope if needed. As soon as the event has propagated even once, the invoked event handler no longer has access to the local variables of the source container. Is this a reasonably correct description?
Event handlers and propagation would be _strikingly_ more useful if the handler had access to the source container environment that created the event, or am I missing something obvious?

* As a second possibility, can I create a _custom task_ (as opposed to a script) that _can_ traverse the object model of a package created in the designer (i.e. I'm not creating the whole package in my own code)?

If you lasted this long, thanks!-)
KI think I answered this on the NG, but the variables on the parent of the eventhandler that raised the event are visible to the event handler.
You can also create a package that handle the events and uses parent package configurations to pass in the values for the events. Then use an execute package task in all your event handlers that references that shared package.
Tasks cannot traverse the object model. They are prohibited and variables will refuse to hold a reference to IS object model objects with an error.|||Although I did implement using those 20 event handlers, I've now ditched that in favour of parsing the needed info out of sysdtslog90, which does away with the need for all those event handlers. Always a good feeling when stripping out half the 'code' but none of the functionality
Very useful to understand how & when to use the event handlers though, thanks!
K

Find the nonexisting Event

Hello everyone I am pretty new with T-SQL and SQL Server 2000 and I need some help or some suggestions on a specific problem. Here goes...

I have 2 tables that Iam using

EVENTS Event_Codes

PersonIdNo EventCodeIdNo EventCodeIdNo EvenCode

6349 13 13 Criminal History

6349 31 31 DL

6349 30 30 CDL

6345 75 75 EMPLOYMENT REF

6345 13 74 Texas Lic

2 CDA

4 Emp Suggestions

I need to find the EventCodeIdNo's that are missing for each PERSON in the Events Table. I've tried creating Arrays and looping through them but I haven't had much success with that.

Thank you,

-DM

Code Snippet

There might be better solutions, if the number of rows are small in events then you could use something like this.

select distinct PersonIdNo, ce.EventCodeIdNo, ce.EvenCode from
(select
PersonIdNo, ec.EventCodeIdNo, ec.EvenCode
from events e
cross join Event_Codes ec) ce
where not exists
(select * from events where events.PersonIdNo = ce.PersonIdNo
and events.EventCodeIdNo = ce.EventCodeIdNo)

|||

Well, assuming you also have a person table, you take a cross join of the persons and events, then remove the ones where there is a match.

drop table eventAttendee, event, person
go
create table person --added because a person might not have attended an event at all
(
personId int primary key
)
create table event
(
eventCode int primary key
)
create table eventAttendee
(
personId int references person(personId),
eventCode int references event(eventCode),
primary key (personId, eventCode)
)
insert into person
select 6349
union all
select 6345
insert into event
select 13
union all
select 31
union all
select 30
union all
select 75
union all
select 74
union all
select 2
union all
select 4

insert into eventAttendee
select 6349, 13
union all
select 6349, 31
union all
select 6349, 30
union all
select 6345, 75
union all
select 6345, 13

select *
from event
cross join person
where not exists ( select *
from eventAttendee
where event.eventCode = eventAttendee.eventCode
and person.personId = eventAttendee.personId)

|||

Louis has provided a nice elegant solution.

I offer this suggestion as an alternative in case you don't wish to involve the Person Table in the query. Depending upon indexing, this should be relatively quick.

SET NOCOUNT ON

DECLARE @.Events table
( RowID int IDENTITY,
PersonID int,
EventCodeID int
)

INSERT INTO @.Events VALUES ( 6349, 13 )
INSERT INTO @.Events VALUES ( 6349, 31 )
INSERT INTO @.Events VALUES ( 6349, 30 )
INSERT INTO @.Events VALUES ( 6345, 75 )
INSERT INTO @.Events VALUES ( 6345, 13 )

DECLARE @.EventCodes table
( RowID int IDENTITY,
EventCodeID int,
EventCode varchar(25)
)

INSERT INTO @.EventCodes VALUES ( 13, 'Criminal History' )
INSERT INTO @.EventCodes VALUES ( 31, 'DL' )
INSERT INTO @.EventCodes VALUES ( 30, 'CDL' )
INSERT INTO @.EventCodes VALUES ( 75, 'EMPLOYMENT REF' )
INSERT INTO @.EventCodes VALUES ( 74, 'Texas Lic' )
INSERT INTO @.EventCodes VALUES ( 2, 'CDA' )
INSERT INTO @.EventCodes VALUES ( 4, 'Emp Suggestions' )

SELECT DISTINCT
dt.PersonID,
dt.EventCodeID,
dt.EventCode
FROM @.Events e
RIGHT JOIN (SELECT DISTINCT
e1.PersonID,
ec.EventCodeID,
ec.EventCode
FROM @.Events e1
CROSS JOIN @.EventCodes ec
) dt
ON ( e.EventCodeID = dt.EventCodeID
AND e.PersonID = dt.PersonID

)
WHERE e.PersonID IS NULL
ORDER BY
dt.PersonID,
EventCodeID

PersonID EventCodeID EventCode
-- -- -
6345 2 CDA
6345 4 Emp Suggestions
6345 30 CDL
6345 31 DL
6345 74 Texas Lic
6349 2 CDA
6349 4 Emp Suggestions
6349 74 Texas Lic
6349 75 EMPLOYMENT REF

|||

Here I give both fast & slow one. According to your data-volume which ever fits use it,

Code Snippet

Create Table #event_codes (

[EventCodeIdNo] int primary key ,

[EvenCode] Varchar(100)

);

Insert Into #event_codes Values('13','Criminal-History');

Insert Into #event_codes Values('31','DL');

Insert Into #event_codes Values('30','CDL');

Insert Into #event_codes Values('75','EMPLOYMENT-REF');

Insert Into #event_codes Values('74','Texas-Lic');

Insert Into #event_codes Values('2','CDA');

Insert Into #event_codes Values('4','Emp-Suggestions');

Create Table #events (

[PersonIdNo] int ,

[EventCodeIdNo] int primary key([PersonIdNo],[EventCodeIdNo])

);

Insert Into #events Values('6349','13');

Insert Into #events Values('6349','31');

Insert Into #events Values('6349','30');

Insert Into #events Values('6345','75');

Insert Into #events Values('6345','13');

Code Snippet

--complex & slow one

select

pes.[PersonIdNo],

pes.[EventCodeIdNo],

pes.[EvenCode]

from

#events es

full outer join

(select Distinct es.[PersonIdNo],ec.[EventCodeIdNo] ,ec.[EvenCode]

from #events es

cross join #event_codes ec) as pes

On pes.[PersonIdNo]=es.[PersonIdNo]

and pes.[EventCodeIdNo] = es.[eventcodeidno]

Where

es.[EventCodeIdNo] is null

Code Snippet

--fast & good one

select

*

from

#event_codes ec

cross join (select distinct [personidno] from #events) p

where

NOT exists

(

select 1 from #events es

where es.[eventcodeidno] = ec.[eventcodeidno]

and es.[personidno]=p.[personidno]

)

|||

Thank you all for your kind suggestions. I went ahead and used Sankar Reddy's suggestions because I have way to many person id and event combos to insert into temp tables. Thanks again for all your suggestions this is a great community!

-Dm