I have a DTS package whose main function is just to execute a stored
procedure that does an "upsert" (updates and/or inserts) to a table
based on a nightly load of data into a scratch table.

In outline, my stored procedure looks like this:

BEGIN TRANSACTION

-- create a temporary table
-- create a unique index on the temporary table WITH IGNORE_DUPE_KEY
-- INSERT rows from the "scratch table" into the temporary table -
the IGNORE_DUPE_KEY "de-duplicates" the scratch table data into the
temporary table
-- do some further manipulation on the temporary table

-- join the temporary table to the target table and UPDATE the target
table based on key matches
-- INSERT records from the temporary table into the target table
where they don't already exist

COMMIT TRANSACTION

This stored procedure runs fine in about 15 seconds when I execute it
from Query Anlayzer.

But if I put it into an Execute SQL Task in a DTS package, it just sits
and spins.

When I look at what's happening with the DTS process using sp_who2, I
can see that DISK_IO does not change, while CPUTime keeps ticking away.
There's nothing blocking the process, and the status shows as
"Runnable."

The same behavior occurs if I modify the DTS package properties so as
to NOT use a Transaction.

If I remove the BEGIN TRAN/COMMIT TRAN from the stored procedure,
recompile it, and try the DTS package again, the package runs fine (I
do get a "step failed" error because of the duplicate keys being
rejected, but the stored procedure runs successfully to completion and
does what it's supposed to do.

Thanks in advance for any advice on this problem!

Best regards,
SteveR
Arnold, MD