Hi there,
Have listed below the problem we encountered and the approaches used to
solve this. Haven't been successful yet :-( Could anyone please help.

DTS Problem definition :

1) Currently, we are using DTS to transform data from our flat file (more
than 10,000 records) to the Staging table (say, TableA).
2) Additionally, we need to insert the StagingtableID (TableA’s primary key
– an auto increment value) and additional information from the flat file into
another table (say, TableB).
3) If the insert into TableB fails, the entire transaction (including the
insert into TableB) should be rolled back.

The Multi-phase data pump approach was used to achieve this purpose.

Approaches and drawbacks encountered out till now :

Approach 1 : Inserting into the TableB at the end of each successful row
insert into TableA. For this, the code was written in the “On Insert Success“
phase of the data pump. The problem ecountered here was that the actual
insert into TableA has not yet been committed and as such the TableA’s ID
(primary key – auto incremental value) was not available.
Since TableA is a common table used by many other modules, getting the
Max(Primary ID) and then incrementing that value within DTS is not a fail
proff approach and we cannot risk it.

Approach 2 : Moved the insert to TableB to the “Pump Complete” phase of the
data pump. Here, the insert into the TableA would have been committed and the
insert into TableB was successful. Problem encountered here was that if, at
any stage, insert into TableB failed, rolling back the inserts into TableA
was not successful.

Approach 3 : To solve the problem encountered in Approach 2, enabled the
“Join Transaction if Present” check box in the workflow properties of the
data pump. Problem encountered here was that, since the commit into TableA
has not gone thru’ yet, the read operation to the TableA to return the
primary ID was unsuccessful –there was a lock on the table by DTS and the
connection timed out. Tried setting the Transaction Isolation level in the
Package properties to “Read Uncommitted” (default being Read committed) with
no positive results.

Approach 4 : Since the DTS package is called by a DLL (component that
requires transaction) thru’ our application, changed code to return an Abort
Pump message from the DTS. Though DTS logged an error in the Application log
to the effect that the DTS package has failed, the records inserted in to
TableA was not rolled back (by the DLL component).

As such, efforts to include the Row-to-Row transform and the Pump complete
phase within a single transaction has not borne any much fruit.

Any suggestions are more than welcome.

Cheers,
Rukmini

Rukmini, If I may ask, why do you have to want to use DTS to perform such a
complex task? You could easily write a C++ program or a Transact-SQL script
or stored procedure to accomplish your requirement. I used to do a what you
are trying to do using C++ classes and/or Sybase stored procedures.
If you must use DTS, have you thought about using the Microsoft
Distributed Transaction Coordinator or alternatively writing a stored
procedure and then executing the stored procedure from a execute SQL Task .
In the following article, Allan Mitchell describes how to handle
transactions:
"Here is what I do for transactions"
"1. Set the package to use transactions and commit on package success"
"2. In the okflow properties of the task I set "Join transaction if"
present" and "Rollback on failure""
"3. Have my tasks execute serially through "On Success" precedence"
constraints"
"4. Make sure the MSDTC is started"

I am just trying to help out until everybody(Allan Mitchell,......)
returns from the July 4th celebrations.





"Rukmini" wrote:

Personally I always stage the text file and never try to do too much with it
"in stream".

Once you have it staged then you can quite easily use either a proc to do
your inserts and use transctions there or you could chain some stored
procs/statements togther in ExecuteSQL tasks and do it that way using
transactions in a DTS package.

Have a look here

http://msdn.microsoft.com/library/de...df_tx_0tmb.asp


--



Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know


"Rukmini" <Rukmini (AT) discussions (DOT) microsoft.com> wrote