Begin Tran and Commit/Rollback Tran in SSIS

• avadhut.joshi 17339

  Mr or Mrs. 500

  Points: 536

  More actions

  April 28, 2011 at 12:12 am

  #238681

  Hi

  

  I am finding following error for transaction.

  Error: 0xC002F210 at CommitTransaction1, Execute SQL Task: Executing the query "Commit Transaction BeginTransaction1" failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Task failed: CommitTransaction1

  please find attached the scrren shot for reference.

  I am trying to have the begin tran and commit/rollback tran.

  Can anyone help on this please.

  Thanks& Regards,

  Avadhut

• brian118

  Hall of Fame

  Points: 3506

  More actions

  April 28, 2011 at 2:51 am

  #1317090

  avadhut.joshi 17339 (4/28/2011)

  ---

  Hi

  

  I am finding following error for transaction.

  Error: 0xC002F210 at CommitTransaction1, Execute SQL Task: Executing the query "Commit Transaction BeginTransaction1" failed with the following error: "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

  Task failed: CommitTransaction1

  please find attached the scrren shot for reference.

  I am trying to have the begin tran and commit/rollback tran.

  Can anyone help on this please.

  Thanks& Regards,

  Avadhut

  Sorry but can't view your screen shot.... does it describe your control flow?

  However, do you have an SQL Task specifying a BEGIN TRANSACTION? since the error is clearly stating that you're committing without opening a transaction.

  Regards
  Brian Ellul
  ----------------------------------------------------------------------------------
  Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
  - Albert Einstein -

• avadhut.joshi 17339

  Mr or Mrs. 500

  Points: 536

  More actions

  April 28, 2011 at 2:58 am

  #1317097

  Hi

  yes its showing the control flow..

  I do have

  1. one execute sql task for begin transaction

  2. then one for delete table

  then rollback for delete

  3. next data flow task

  4. rollback for data flow..

  Thks&Rgds

  Avadhut Joshi

• brian118

  Hall of Fame

  Points: 3506

  More actions

  April 28, 2011 at 3:22 am

  #1317106

  Did you set the 'RetainSameConnection' property to True on your connection (from the connection manager)?

  Regards
  Brian Ellul
  ----------------------------------------------------------------------------------
  Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
  - Albert Einstein -

• avadhut.joshi 17339

  Mr or Mrs. 500

  Points: 536

  More actions

  April 28, 2011 at 3:24 am

  #1317108

  if I do it then only 75% of my containers run properly last two containers dont get run..so is there any issue with that..am I doing any mistake or need to do smthing more..

• brian118

  Hall of Fame

  Points: 3506

  More actions

  April 28, 2011 at 3:39 am

  #1317113

  avadhut.joshi 17339 (4/28/2011)

  ---

  if I do it then only 75% of my containers run properly last two containers dont get run..so is there any issue with that..am I doing any mistake or need to do smthing more..

  I'm afraid that you'll need to set the RetainSameConnection property to true for your Begin/Commit transactions to work! It seems that you have an issue with your last two containers and you'll need to dig further to identify what is happening there.

  btw, did you correctly set the 'Transaction Option' of your components?

  Regards
  Brian Ellul
  ----------------------------------------------------------------------------------
  Computers are incredibly fast, accurate, and stupid. Human beings are incredibly slow, inaccurate, and brilliant. Together they are powerful beyond imagination.
  - Albert Einstein -

• avadhut.joshi 17339

  Mr or Mrs. 500

  Points: 536

  More actions

  April 28, 2011 at 3:42 am

  #1317115

  hmmmmmmmm

  I have set transaction option as supported for all.

  I am not able to get through what is exactly happening in case of those two containers as they are un touched..ok may be because of roll back its happening..let me check..thanks

  Avadhut Joshi

• avadhut.joshi 17339

  Mr or Mrs. 500

  Points: 536

  More actions

  April 29, 2011 at 5:49 am

  #1317742

  Hi

  still its giving the following error in some cases of rollback

  The Begin statement for the Rollback is not given.

  Thanks & Regards,

  Avadhut Joshi