I am currently having an issue with SSIS where I created a package, copied
that package and added it to the existing project, renamed the copied
package, and made modifications to that package so that it performed the same
functions as the first package, but on different data.

Because I need this type of "same functions, different data" scenario is
needed for multiple data, I thought I could simply create exactly what I
needed, copy that, and modify the copied version for each of the items I
needed.

This works great when I execute the packages individually; however, when I
try to bring the packages together by using a parent package, it seems that
when one of the child packages errors out, that multiple appear to error out,
yet when you look at the control and data flows, there are no errors.

Has any one experienced this and/or is there a work-around/fix for this
behavior?

Hello Vorta,

Do you have any error messages for us?

Can you tell us how the package looks so we can try to repro?

Allan

Well, I wasy trying to do better than that but the news server won't let me
attach my small sample project I created.

At the bottom of this post, I describe how to setup a test project. In the
mean time, here is what I am seeing with this example project.

_____
Run "Package 1": Results will produce a run that is fully successful (all
tasks green)
Run "Package 2": Results will produce a run that errors on the second task
Run "Package 3": Results will produce a run that errors on the first task.

The above results are correct for each of the packages.

Run "Parent Package": Results on the "Parent Package" control flow will
show packages 2 and 3 failed. Click on the tab for "Package 1".
Interestingly, even though this package was successful, you see that tasks 1
and 2 failed. Clicking on "Package 2" and "Package 3" tabs show the same
results. This is an example of one of the issues I'm seeing. Additionally,
in my project, I'm also getting task progress entries, on the progress tab,
from tasks being placed under the wrong task. I don't see this occuring in
this particular example, but I didn't work too hard constructing the example
either.

Here's another: Make sure you're not running any package. Click on
"Package 3" tab. Set a break point on the first task of "Package 3". Click
on "Package 2" tab. The environment has kindly put a break point in
"Package 2" for you as well (as well as "Package 1").

Here's what I believe is occurring: When I copied the original child
package "Package 1" and made modifications to it under the names "Package 2"
and "Package 3" that the ID's of the tasks remained the same for those
packages. This does not seem to be an issue when running the packages
separately. However, when run under a common parent, the engine appears to
have multiple tasks with the same ID but the reference to the task not
qualified by the package to make it unique, and thus gets confused as to the
status of each task. If this is the case, this is a severe limitation for
me constructing packages that are extreemly similar to each other by
utilizing the copy/paste function for a package.
____
Sample Setup:

Create a package called "Package 1". Create three variables (var1, var2,
var3) of the int32 datatype with default value.
Add 2 script tasks to the package (Task1, Task 2).
Add var1 and var2 as read-write variables to Task 1.
Add var1 and var2 as read variables to Task 2 and var3 as read-write.
Under Task 1 script, set var1 and var2 values to some integer values.
Under Task 2, set var3 as a sum of var1 and var2.
Run the package: package should execute with no errors.

Copy and paste "Package 1" in the project explorer. Rename the copied
version as "Package 2". Paste again rename as "Package 3".

Modify Package 2 to error out in Task 2. I divided the sum by 0 within a
try block and set the status of the task to fail in the catch statement and
exited the function. Runing this should produce error on Task 2.

Modify Package 3 to error out in Task 1. I divided a number by 0 within a
try block and set the status of the task to fail in the catch statement and
exited the function. Running this should produce error on Task 1.

Create a package called "Parent Package". Add three independed Execute
Package flows and set each flow to execute one of the three packages.

You now have a project that will produce results as described above.

Hope this helps!


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Has anyone been able to take a look at this? I hate to open a support
ticket if it's a known issue...

"Vorta" <Vorta (AT) noemail (DOT) noemail> wrote

Hello Vorta,

OK so i have followed through your repro. i can confirm that;

1 All the tasks will fail in all the packages
2. Set a Breakpoint on one task in one package and the corresponding task
in all the other packages has a breakpoint set.
3. All 3 Package IDs are the same
4. All Task IDs are the same.
5. This happens if the ExecutePackage tasks in the calling parent package
are free to execute all at once.
6. The only BP to be hit will be the one on which it is actually set

There is clearly confusion happening here but the way I solved it was to
have the packages executed sequentially by using Workflow. This causes everything
to work as I would expect. This does not mean I do not think that the behaviour
you are seeing and indeed I have seen is both undesirable and incorrect.



Allan

Just as an FYI for all... I did submit this to Microsoft Support. It has
been identified as a bug and I was told the fix for it should be on SP2.

The workaround is to create a new package (not copy the existing one) then
copy the tasks from the original package to the new one. This will generate
new IDs for each task. If you have any components which reference local
datamanagers you will need to modify those tasks to point to the new version
of that data manager. Also, if you have any script tasks, the script for
that task DOES get copied, but you must open up the task and edit and close
the actuall script for it to "compile" the script, otherwise, the task will
show that there is an error that the script has not been compiled.

"Vorta" <Vorta (AT) discussions (DOT) microsoft.com> wrote