| Groups | Blog | Home |
sql server dts : dts transaction without dtc
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 [quoted text, click to view] "Chuck P" <nospam@crlf.com> wrote in message news:2767FEC2-E655-4CF3-919B-23953F335CD3@microsoft.com... > 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, >
In message <BF0C8916-ABD8-499E-98B4-918BDB06429E@microsoft.com>, Chuck P
<nospam@crlf.com> writes [quoted text, click to view] >I don't mind not using the DTS package transaction. >How do you do it at the T-SQL level? 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 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, [quoted text, click to view] "Darren Green" wrote:
> 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@crlf.com> wrote in message > news:2767FEC2-E655-4CF3-919B-23953F335CD3@microsoft.com... > > 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, > > > >
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. [quoted text, click to view] "Darren Green" wrote:
> In message <BF0C8916-ABD8-499E-98B4-918BDB06429E@microsoft.com>, Chuck P > <nospam@crlf.com> writes > >I don't mind not using the DTS package transaction. > >How do you do it at the T-SQL level? > > 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 >
In message <9D71797E-57CF-4860-A707-BBF404318A64@microsoft.com>, Chuck P
<nospam@crlf.com> writes [quoted text, click to view] >I was hoping to put in an active x script to access the packages oledb This is not possible.>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. > [quoted text, click to view] >I wanted to keep the tables online 7x24, that's why I was thinking of >transactions. 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. [quoted text, click to view] >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 <BF0C8916-ABD8-499E-98B4-918BDB06429E@microsoft.com>, Chuck P >> <nospam@crlf.com> writes >> >I don't mind not using the DTS package transaction. >> >How do you do it at the T-SQL level? >> >> 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 >> >> -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |