HI,
I'm not able to run my package when I prepare it for transactions.

Case: I have 5 packages and each of them have some 6 to 15 Tasks and each
package has 'Use Transactions' property checked and each task has 'Join
transaction' property checked. I run all these packages in a mother package,
because I want all the packages as part of the transaction (all or nothing).
When I run to test it through designer, my package hangs.

I have msdtc running.

can someone throw light my problem.

Thanks in advance,
Rgds,
Mabbu

Can you see where it is hanging?
Does each package run individually?


--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mabbu Bharat" <m_bharat (AT) hotmail (DOT) com> wrote

OK

Can you change the isolation level from Serializable to the default of Read
Committed.

When you execute the package (serializable) have a look on the server to
which the connection pertains and my guess is that if you execute sp_who2 in
QA you will see blocking.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mabbu Bharat" <m_bharat (AT) hotmail (DOT) com> wrote

Your ExecuteSQL task is failing (DeleteData)

Can you show me

1. The workflow properties the for task
2. The statement inside?

Take the statement out from the task and look in QA.

Execute there?

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mabbu Bharat" <m_bharat (AT) hotmail (DOT) com> wrote

And it works in QA....

-Mabbu

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

Your workflow properties look fine.

I would try this

1. Get rid of the GO statements - retry
2. Seperate out the 3 DELETE statements to their own task - retry


Also have the package log to text file and see if the error is better
defined there.



--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Mabbu Bharat" <m_bharat (AT) hotmail (DOT) com> wrote

In message <80958627-DBDD-4366-8371-7377D168213F (AT) microsoft (DOT) com>, Hari
<Hari (AT) discussions (DOT) microsoft.com> writes

Incorporating Transactions in a DTS Package
(http://msdn.microsoft.com/library/de.../en-us/dtssql/
dts_addf_tx_2n8l.asp?frame=true)

What you describe is possible, so I suggest you start with the above
section of Books Online. If you have questions after this then please
post back with specifics.

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Dear Darren, let me tell you what i did.
i have enabled join transaction and rollback on failure on all the three execute package workflow properties and enabled commit on success for the last one. and i set use transaction and commit on success for the package properties.

But even then the first two packages are running successfully and the third one is failing, but there is no rolling back occuring. And i am also getting some error like dtsdatapump failed to join or unable to join.
is there anyother way to do this.

"Darren Green" wrote:

In message <9C701BD2-86A5-4B3A-89D3-34DF47332281 (AT) microsoft (DOT) com>, Hari
<Hari (AT) discussions (DOT) microsoft.com> writes
If you have set rollback on failure for teh Exec Pkg Tasks, then you
must ensure that the child package is set to fail on first error, so
that any error is raised up to the parent.

As for the datapump issue, the full error would be useful, but to start
with try this-

279857 - BUG: Error 3910, "Transaction Context in Use by Another
Session"
(http://support.microsoft.com/default...9857&Product=s
ql2k)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org