Firstly, thanks to Darren Green for solving my previous problem!
("When one task joins a transaction, all successors must?")
Now, it seems I can mix and match those joining and those not joining
the transaction successfully
(so long as each non-joiner brings it's own connection to the party;
it makes sense really)



However, the full scope of my problem means that I need to run 2
packages:
one to install a database;
another to upgrade it.

The short:

Two packages are run in succession in their own processes are:

1. Package 1: (create the database) UseTransaction is false. It
works!
2. Package 2: (upgrade the database) UseTransaction is true This
package
has ranging content, but if it contains *any* INSERT for the any
table
belonging to the new db, then the this package fails to commit.

Isolation level is repeatable read (testing serialisable now)

The Details:

Install and Upgrade are in separate Packages because a conversion
"package" must (optionally) run in between these steps (this 'importer
package' is provided by a proprietary converter and is NOT DTS
Package, nor is it really part of the problem. Let's just accept that
these two Install and Upgrade packages need to be separated)

Unfortuately, if the two Packages are executed back to back (create db
package, then upgrade db package) the singular error that I get at the
end of the second (and final) Package is:

COMException -2147168234
Commit of Package Transaction failed after executing all Steps. The
transaction status is in doubt. A communication failure occurred, or a
transaction manager
or resource manager has failed.

This also STOPS the MSDE instance Service in its tracks

Now, this error only occurs (seemingly, after many trials)

* on MSDE instances (SP3, SP3a have been tested)
* when the first package has been called "to create the db", then the
second
is called AND it contains ANY table content altering statement
(like an
insert or delete)

If I stop the MSSQL$INSTANCE service, then restart it. I *DO* get
satisfaction, but then there are other errors, for example: if I run
the two
packages in the same process (which is what will happen eventually) OR
one after
the other in quick succession, I get "Couldn't find database ID = 5.
May not be
activated yet." or something like that. I'm not interested in this
error. I'm hoping to do this properly: not to have the nasty original
error come up,
and not to need to stop and start services as a (rather ineffectual)
work- around.


Some extra details:

* I haven't tried to run the importer in between. It's not really
relevant
to testing. The packages need to be able to work consecutively in
one
scenario anyway, so I haven't bothered complicating things with
the
optional middle step.

* the first package (create database) has been tested with or
without the
"UseTransaction" package property set. The UseTransaction
property is
always on for the second package; if it is set to false (and the
first
package is false also) it all works: but that's not terribly
helpful in an
upgrade scenario if an error occurs; I need to be able to
rollback, rather
than leave the machine in an unknown state.

* sharing or not sharing connections within the package results in
the same
problem

Thanks in advance,

T