Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

One small step for the SSIS team, one giant leap for SSIS developers

 

That’s what I twittered recently upon first opening up Release Candidate 0 of SQL Server 2008. You may remember that back in March of this year I told you about a new feature that would be appearing in a future CTP; namely that a container would provide information about its parent container. Hence if you peer into the depths of this latest release then you will discover that some new system variables have been introduced. Every container in the control-flow now has a system variable called @[System::ParentContainerGUID] scoped to it that contains, quite simply, the ID of the container’s parent container. The following screenshot shows three instances of this variable:

image

Why are there 3? Very simple, the package has three containers (which I have highlighted above) and each one of them has an instance of @[System::ParentContainerGUID] scoped to it. Note how the instance that is scoped to the package has an empty value – this makes sense of course because the package container does not have a parent.

Additionally, and more importantly, every eventhandler container also has a new system variable scoped to it called @[System::SourceParentGUID].

image

This variable contains the ID of the parent of the container that raised the event. Let me explain that in the context of the package depicted above; any eventhandler that (for example) handles an event thrown by “SEQ Inner Sequence Container” will have the ID of “SEQ Outer Sequence Container” stored within @[System::SourceParentGUID].

In the demo package depicted above I have a script task in the OnPostExecute package-scoped eventhandler that throws out a message box containing some information about the container that raised the OnPostExecute event. Here are the message boxes it threw up:

image

image

image

N.B. You may want go and read my blog entry on event propagation to understand why the package-scoped eventhandler captures events raised by all three control-flow containers.

Note how @[System::SourceParentGUID] for the event thrown by “SEQ Inner Sequence Container” contains the SourceID of “SEQ Outer Sequence Container” and also how the @[System::SourceParentGUID] for the event thrown by “SEQ Outer Sequence Container” contains the SourceID of the package container. Also note, again, how @[System::SourceParentGUID] for the package container is empty as you would expect.

If you want to run this simple demo for yourself then go ahead and download it: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20080607/SimpleParentContainerGUIDDemo%20package.zip

The package doesn’t access any external objects so you can literally drop it on your hard drive and execute it using dtexecui.exe. Note that you will need to have Release Candidate 0 of SQL Server 2008 or later installed.

Within this blog entry I’ve introduced you to the new system variables:

  • @[System::SourceParentGUID]
  • @[System::ParentContainerGUID]

in SQL Server 2008. In my next blog entry I’ll explain how you can make use of them.

-Jamie

Comments

 

Michael Barrett said:

Interesting... I wonder if the ParentContainerGUID is populated at runtime for the package, if the package has been called by a "parent" package using the "Execute Package" task?!

June 8, 2008 8:48 AM
 

jamie.thomson said:

Good question Michael. I'll be answering it in a future blog entry (I've been told the answer is 'yes' by the way)

-Jamie

June 8, 2008 8:54 AM
 

SSIS Junkie said:

In my blog entry Custom Logging Using Event Handlers from June 2005 I described an easy technique for

June 8, 2008 12:42 PM
 

Matt Olson said:

I'm trying to think of practical uses for this and the only thing I can think of is that this would be used for providing better error logging in an email message for instance to tell at what container the error occurred in.  

June 20, 2008 7:55 PM
 

jamie.thomson said:

Hi Matt,

You're absolutely right. Well, almost right, it would provide better logging full stop, not just better logging of errors.

I believe that to be hugely important. I take it you don't agree?

-Jamie

June 23, 2008 9:31 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems