Package crosstalk?

I'm trying to convince myself that I am not crazy...

I'm having some odd things occur with a couple of child packages being called from a parent package. Inside the master package, I have two different Execute Package tasks that are contained in two different ForEach Loop containers. Both of these containers are downstream of another container. The only Precedence Constraints on either is "Successful".

One of these child packages loads a Household dimension table. The other loads a Customer-to-Account bridge table . I built each child package with a template, so there are many of the same variables, containers, etc.

The first time I ran the master package I noticed my Household package failed because its Flat File Connection Manager was trying to pick up the file my Bridge package needed. I then disabled the container in the master package with the Bridge task so that only the Household package would run. The next time I ran it, I put some PreExecute breakpoints in the Household package. When the breakpoint was hit, the Locals tab showed a mix of Household variable values and Bridge variable values.

So I went to open my Bridge package and the breakpoints I had set in the Household package were also set on the Bridge package. Yet, I had the Bridge package closed when I set the Household breakpoints. If I unset a breakpoint in one, it automatically unset it in the other.

I had seen this behavior before, but it cleared up and I guess I assumed I had dreamt it all up. Anyone else have any experience with this?This may help-->http://sqljunkies.com/WebLog/knight_reign/comments/5366.aspx|||I appreciate the link, R.K.S. Rather than helping me out, though, it's validating that what I'm seeing is bug-like.

I mentioned that the two child packages have like-named variables and that these two packages most likely would be executed at the same time as a result of their parallel position and precedence criteria following an upstream container.

The link states that the variables defined in the child packages themselves are hidden from each other. But with the parallel positioning of their respective Execute Package tasks in my master package, that's not what I'm seeing.

As a test, I moved the position of the Bridge's Execute Package tasks further downstream in my master package. The Household package picks up the correct values in its variables and finishes with no problem.

Curiously, the setting and unsetting of breakpoints across the two packages still occurs.|||Sorry, haven't had much experience with the situation you're describing. I do have a similar problem where I have two child packages that run fine independently but when I run them from a parent the variables in each respective child seem to "step on each other", for lack of a better term. The children were created from the same original package and contain the same variable names. All variables are scoped at the package(child) level.|||

mr_superlove wrote:

Curiously, the setting and unsetting of breakpoints across the two packages still occurs.

This may occur if one of the packages was a copy of another (e.g. you copied one package file and added it as second package). This can confuse the designer, as both packages have the same IDs and the tasks inside them have the same IDs, so the designer can't distinguish between them.

To fix this condition, open one of the packages (or both), make sure the package object is selected in Properties panel, find a property called "ID", click the drop down at the right and select 'Generate new ID'. Save the package. This should fix the problem.

|||Thanks, Michael. This partially works. Certainly at the package level generating a new ID prevents the package-level breakpoints from being confused. I can set the OnPreExecute breakpoint on my Household package and it not propagate to the Bridge package.

I'm still seeing confusion on breakpoints set at the container level, however. The IDs for these objects are grayed out. Any way to generate those?

I assume the like IDs are also the reason for the variable value cross-pollination I was seeing on my copied packages executing at the same time. Any way to just regenerate the whole lot of IDs? Thanks.|||

mr_superlove wrote:

I assume the like IDs are also the reason for the variable value cross-pollination I was seeing on my copied packages executing at the same time. Any way to just regenerate the whole lot of IDs? Thanks.

Here is an KB articule on how to re-generate package IDs

http://support.microsoft.com/kb/906564/

Rafael Salas

|||Thanks, Rafael, but I believe this link merely states an additional command line method of doing what Michael posted.

It doesn't address task IDs within the packages themselves. For instance, here are the IDs for the data flow task in each package:

{BD576B04-E131-479C-B720-75848C51F9E6}
{BD576B04-E131-479C-B720-75848C51F9E6}

These are grayed out and, at least within BIDS, static. I suppose I could modify the IDs manually in a text editor, but I shudder to think the problems that could cause.

It's a great learning experience, though. It certainly speaks to problems I've had in the past with copying data flow components. I'm not crazy after all.|||

I've had a problem with the fact that every "named" object is eventually reduced to a GUID, and only referenced by the GUID during execution (at least, that's how it appears). I believe you're bang-on that the problem you're seeing is caused by the same GUID being used to ID two different objects.

This is precisely why I've pretty much stopped copying and pasting objects -- BIDS does not do a good job of assigning new GUIDs to objects pasted into a project from the clipboard. I've had 'way too many problems because of this issue. (I really wish Microsoft had use a more Visual Studio-like paradigm, where each object was a discrete file like in a VB.NET or C# project. This reliance on XML is just too inefficient.)

I also believe you're going to have to manually edit the DTSX file using a text editor to fix the problem. There are several issues you're going to encounter, the first being that when you search for a GUID in the DTSX, how are you going to tell which object it references? If you manually update the GUID (like, changing the "E6" at the end to "E7", making sure that {BD576B04-E131-479C-B720-75848C51F9E7} doesn't already exist in the DTSX file), you have to be sure to update only the GUID that refers to Data Flow Task 2, and not Data Flow Task 1.

I've actually had good luck directly editing my DTSX, when needed. In many cases it's not to hard to figure out which block of XML referes to which object, based on the context in which it appears (other values around it, etc.). So far I've been able to patch in variables, variable assignments, configuration information, and other stuff. But, nothing quite as ambitious as you need.

My guess is that, other than completely rebuilding one of your data flow tasks, the only fix is going to be to directly edit the DTSX file (after backing it up, of course!).

Good luck!