I would like to use transactions in dts without using dtc (firewall issues;
no chance of reconfiguring). Is there a way to do this? I am only updating
on one connection on a sql 2k database.

thanks,

No. To use a package transaction, it will require DTS. You may only use one
connection, but DTS will not know that in advance. You have the ability to
span connections, so this requires DTC, and without it DTS cannot allow you
to proceed, otherwise it will be to late.

Can you not design your package in such a way that you do not require a DTS
transaction, and instead rely on transactions at the T-SQL level, for a
single batch in your connection.


--
Darren Green
http://www.sqldts.com
http://www.sqlis.com

"Chuck P" <nospam (AT) crlf (DOT) com> wrote

I don't mind not using the DTS package transaction.
How do you do it at the T-SQL level?
I wasn't sure how to get the connection object.
Do you use ScriptPkg tool or something?
thanks,


"Darren Green" wrote:

In message <BF0C8916-ABD8-499E-98B4-918BDB06429E (AT) microsoft (DOT) com>, Chuck P
<nospam (AT) crlf (DOT) com> writes
You just write a T-SQL script as you would to run in query analyser, and
call through the Exec SQL Script. You can use a transaction in the
script, about which DTS neither knows or cares. Nothing special.

It depends what you are actually doing, I don't know if this will work
for you.


You can also design the package so that workflow is used so that changes
are only made when appropriate. Rollback is the difficult bit, but is it
required?

I download data, but do some checks before and after, and compare. I
then move data from staging tables to main tables (T-SQL level
transaction). If all is then move I delete the data from the source. No
need for package transactions, because if my checks fail, then I stop,
nothing gained, nothing lost. If the Exec SQL Task fails, same thing.
For me the only stage that I cannot repeat, or rollback is the final
delete, so that happens when everything else is OK. If the delete fails,
you have 24 hours to solve it!

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

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

I was hoping to put in an active x script to access the packages oledb
connection (I am sure it has one somewhere) and then start a transaction.
Next do several data transforms (inserts from several datasources).
Then another Active X script to commit the transaction if their were no
package errors.

I wanted to keep the tables online 7x24, that's why I was thinking of
transactions. I once saw a huge dts package that had routines to backup the
tables, do transforms, check for existance of trasform error logs, restore db
if errors. Your method is interesting because I can rollback the
truncate/insert from the temp table.


"Darren Green" wrote:

In message <9D71797E-57CF-4860-A707-BBF404318A64 (AT) microsoft (DOT) com>, Chuck P
<nospam (AT) crlf (DOT) com> writes

Personally I think transactions can reduce the availability of tables in
this situation. Take it out lots of locks to enable the transaction
state to be maintained, can block the the OLTP quite seriously. The less
work you do on the OLTP (source), the less impact. Locks and
transactions are an impact. As ever it is a trade-off.

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

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