Transaction Rollback Issues
Hi
We are loading data from one extract file in to 4 different database tables. When we are loading if the first table gets loaded without any issues and the second table loading gives any issues, we need to rollback the data loaded in to the first table also.
In the package for achieving this, we are using Sequence container. In the sequency container we are having 4 different tasks for loading data to 4 different tables from the same extract file. We also tried setting the different transaction options given in the properties box of the packages, container and tasks.
Also we tried the properties by setting as mentioned in the Test# 7 and 8 in the following URL
Could you please explain what properties we need to give.
Thanks
Kumaran
[2194 byte] By [
KumaranS] at [2007-12-23]
Hi Jamie
Following scenarios we tried with TransactionOption :
Package= Required
SequenceContainer=Supported
All the 4 Tasks = Supported
The following is the error i am getting
[Slowly Changing Dimension [6136]] Error: The AcquireConnection method call to the connection manager "conApplnData" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "Slowly Changing Dimension" (6136) failed the pre-execute phase and returned error code 0xC020801C.
Note: "conApplnData" is the connection manager for database table. I am using Slowly Changing Dimension for the 4 different tasks to load data in to tables from same extract.
Thanks
Kumaran
Hi Jamie
After i got the above said error message,i am also getting this below error.
[Execute SQL Task] Error: Failed to acquire connection "conApplnData". Connection may not be configured correctly or you may not have the right permissions on this connection.
But i am not getting any error message related to MS DTC.
Could you please confirm me that the transaction properties i assigned is fine or should i need to change the transaction property in any of the component.
Thanks
Kumaran
Hi Jamie
I executed the package with the default settings for the transactions option.
I.e for Package -- Supported , Container - Supported , Task - Supported.
The package is executed fine and executed each of the 4 tasks and loaded data in to 4 different tables without any issues.
Once i changed the default transactionsoption property only, i started getting errors.
Could you provide me more info.
Thanks
Kumaran
Hi Jamie
As you said, i checked the MS DTC in services of the client machine.
The configuration seems to be OK and is as below:
Service Name: Distributed Transaction Coordinator
Status : Started
Start Up : Automatic
Log On As : NT AUTHORITY\NetworkService
Should i need to check the MS DTC in server machine too.
Please provide me some more info.
Thanks
Kumaran
