Archive for October 2009

PASS Summit 2009

I’m really looking forward to the PASS Summit next week, and getting a chance to visit with a lot of the people in the community that I interact with on a regular basis. It’s going to be a really busy week, as there’s a lot of great sessions that I’m looking forward to attending, and a few things that I’m going to be delivering myself.

A quick summary of where I’ll be during the conference:

Outside of that, I’ll be around at other sessions, the evening events, and in the “Ask the Experts” area. Looking forward to seeing everyone there.

SQL Server MVP Deep Dives

There’s a new book available for pre-order – “SQL Server MVP Deep Dives”. This book is a little unusual in that 53 MVPs came together to contribute 59 chapters to the book. Some of the best SQL Server authors in the world contributed chapters to it. I’m certainly not one of that group, but somehow, I managed to get included, and it’s a great honor to be in such good company. The book covers a wide variety of SQL Server topics, including:

  • design
  • development
  • administration
  • tuning and optimization
  • and business intelligence (my personal favorite)

This book was a special project for the authors involved. 100% of the author royalties go to War Child International, which is a charity that works to help children affected by war across the world.

So, if you like the idea of learning some interesting things about SQL Server and helping children at the same time, get this book. If you are attending the PASS Summit, it will be available for purchase from the conference bookstore onsite. There will be a large number of the authors at the Summit (including me), so there will be plenty of opportunities to get your copy signed.

Passing an Object from a Parent Package To a Child

Occasionally, you may run into the need to pass values between packages. In most cases, you can use a Parent Package Variable configuration to pass the value of a variable in the calling (or parent) package to the called (or child) package. However, Parent Package Variable configurations can’t be set up on variables of type Object. Fortunately, you can work around this pretty easily, thanks to the fact that the calling package variables are inherently accessible in the called packages. 

I’ve set up a sample parent and child package to illustrate this. The parent package is straightforward. It uses an Execute SQL task to populate an object variable named TestVar with a recordset object, and then calls the child package.

image image

The child package has a Foreach Loop Container to iterate over the recordset object. It has a Script task that is used to copy the parent package’s variable (TestVar) to a local variable named LocalVar. This is the variable that the Foreach Loop is configured to use. Why copy the value? If you don’t have a local variable to reference in the Foreach Loop, it won’t validate properly.

image image

The script in Copy Variable is pretty simple. It relies on the fact that you can reference parent package variables inherently, as they are included in the collection of variables accessible in the local package. The script just copies the value from one variable to the other, so that the Foreach Loop will have something to do.

public void Main()
{
    Variables vars = null;
    Dts.VariableDispenser.LockForWrite("User::LocalVar");
    Dts.VariableDispenser.LockForRead("User::TestVar");
    Dts.VariableDispenser.GetVariables(ref vars);

    vars["User::LocalVar"].Value = vars["User::TestVar"].Value;
    vars.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
}

Please note that for this to work, you cannot have a variable in the child package with the same name as the variable in the parent package. If you do, the local variable will hide the parent variable. Outside of that, this works really well for passing object values between packages. The same technique can also be used in reverse to send values back to the parent package, if you have that need.

The sample has been uploaded to my Skydrive. Let me know if you have any questions.