none
Transaction Problem. RRS feed

  • Question

  •  

    Hi,

     

    I have a package in it there is a Execute SQL task and Data Flow Task. Execute SQL task truncates a table(Table1) and Data flow Task loads Table1. Without Transaction option set I am able to run the package, but when i set package Transaction property  to "Required" and on tasks i keep them as Supported, error comes up.

     

    Error Mwessage is:

     

    "The transaction has already been implicitly or explicitly committed or aborted".

     

    I have tried setting many options prescribed in this forum but still not able to run package.

     

    Source for Table1 is in different server and Table1 is in different server on which package runs.

     

    Thanks in advance.

    Monday, November 19, 2007 11:29 AM

All replies

  • This is only a guess, but I would look at how the minimally-logged nature of the TRUNCATE TABLE statement is interacting with the distributed transactions that SSIS uses. Since SSIS uses the DTC for its declarative transactions, and since TRUNCATE TABLE only logs the fact that the table was truncated (it does not log the data in the table) the two may not be compatible.

     

    With that said, I also did a little research - do any of these help?

     

    http://killspid.blogspot.com/2007/10/ssiss-interaction-with-dtc.html

     

    http://www.bokebb.com/dev/english/2006/posts/2006108101.shtml

     

    Good luck! 

    .

    Monday, November 19, 2007 1:05 PM
    Moderator
  •  

    Hey thanks for ur reply. I tried using your suggestions but the error message exists.Any other suggestions.
    Monday, November 19, 2007 3:00 PM
  • Did you try using a DELETE instead of TRUNCATE TABLE?

     

    Monday, November 19, 2007 7:17 PM
    Moderator
  • Yes i have used delete statement but still th eproblem persists.

     

    Tuesday, November 20, 2007 6:10 AM
  • If you get the same behavior with both TRUNCATE TABLE and DELETE, can you please provide as much detailed information as possible about what the package does, especially what sources and destinations you're using within the data flow or with any other tasks that are contained within the transaction?

     

    Also, are there any other error or warning messages being created, either in the SSIS output, or in the Windows Event Log?
    Tuesday, November 20, 2007 12:57 PM
    Moderator
  •  

    My package Has a Execute SQL task it truncates\delete a table(table1) and a Data Flow Task which loads that table(table1). Source for Table1 is present in another server. Both these use OLEDB connection. I have kept my package property for transaction as required and on the two rasks as supported.Error message which i get is as follows:

     

    [Connection manager "1231.DUMMY.sa"] Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00E "The transaction has already been implicitly or explicitly committed or aborted".

     

    It works fine if my source table and destination table are in same server.

     

    Thanks.

    Tuesday, November 20, 2007 1:11 PM
  • This sounds to me like you do not have the DTC properly configured.

     

    Tuesday, November 20, 2007 1:39 PM
    Moderator
  • Hey i have followed certain steps given in some blogs. Can u suggest me which setting have i done wrong.wil give details of my settings in short while.

     

    Thanks

    Tuesday, November 20, 2007 2:03 PM
  •  

    In services DTC is Running.

     

    In security settings for MSTDC I have Enabled  Network DTC Access,Allow Remote Clients and Allow remote administration. Allow Inbound and Outbound.

     

    Anything else i have missed.

    Tuesday, November 20, 2007 2:09 PM
  • I have the same problem - did you resolve yours?

     

    I've was told also to check Activate Transaction Internet Protocol (TIP)

     

    but still I have the same error.

     

    Wednesday, December 12, 2007 10:01 AM
  • No i still have the problem..

     

    Wednesday, December 12, 2007 11:18 AM
  •  

    My problem is related to the fact that the workstation is running in one domain and the server i running in another domain.

     

    So this is what I've tried so far:

    (Domain 1 is starting the transaction)

     

    Domain 1                      Domain 2               Result

    Workstation (XP SP2)    Server 2003 SP2     Failed

    Server 2003 SP?            Server 2003 SP2    Success!!!

     

    Same domain

    Workstation (XP SP2)    Server 2003 SP?     Success

    Workstation (XP SP2)    Workstation2 (XP SP2)  Success

     

    The interessing part is that is actually works bewteen 2 different servers - this could have something to do with different setup on the workstation and the server which I will try to find out tomorrow....

    Wednesday, December 12, 2007 4:38 PM
  • We put the workstation on same physical network as the server and the error dissapeared. So in our case it has something to do with the router/switch - maybe a port that DTC uses that is blocked between the networks.

     

    Maybe the information can help you too, kv_sit?  - please let me know.

    Thursday, December 13, 2007 10:16 AM
  • Hey gr8 u found solution.

    In my case domain are same,in same network.

    so do not what the problem is and finally decided to drop using transactions.

    But will find solution for further implementions.

    Could you share some finer points which i might have missed that would be helpful.

     

    Thanks a lot.

     

     

     

     

    Wednesday, December 19, 2007 1:12 PM
  • I had the same problem, and it turned out to be a firewall issue for me.  I configured the firewall to allow DTC traffic on both the client and server and then the error went away.

    Monday, March 1, 2010 10:29 PM
  • HI,

    I had same issue (Look at this post:  http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/dfb5a8b9-d43c-452b-bb21-d6aea192fc97).

    I have configured DTC on my desktop pc and also on sql server but still I had same issue. Now I have added the msdtc.exe into firewall exception in my desktop and the error resolved. Try this and I am sure this will help you.

    Thanks,
    Prabhat
    Sunday, August 22, 2010 8:13 AM