tag:blogger.com,1999:blog-2303058199815958946.post3724850324109665899..comments2024-03-28T10:36:35.016+01:00Comments on Microsoft SQL Server Integration Services: SSIS Transactions with TSQLJoost van Rossumhttp://www.blogger.com/profile/01125981589974671317noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-2303058199815958946.post-75615362620906656692015-09-08T15:32:48.097+02:002015-09-08T15:32:48.097+02:00HI, We did use "RetainSameConnection" pr...HI, We did use "RetainSameConnection" property but still had the issue. <br />Ok, we got the design working after much deliberation. The issue was due to the Greenplum driver not being able to understand what SSIS wanted it to do. We set the design as in my previous post and got an updated version of the driver. Set DTC on on the HAWQ server and there you go.<br />Also seemingly there was an 'gather autostats' switched to 'on' on the HAWQ server,this was causing SSIS to timeout waiting to start a fast load into a table. (thought i mention this)Anonymoushttps://www.blogger.com/profile/04501836851522408071noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-43379184658117238842015-09-08T10:58:20.086+02:002015-09-08T10:58:20.086+02:00Thank you very much for posting this design patter...Thank you very much for posting this design patter. It helped me very much. Works also wtih ODBC connection, so I could use it towards PostgreSQL.Anonymoushttps://www.blogger.com/profile/07339242766369350490noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-39292868074299874272015-09-08T09:33:35.074+02:002015-09-08T09:33:35.074+02:00@Ljubinka: Are you getting any errors? Is it a lot...@Ljubinka: Are you getting any errors? Is it a lot of data that you try to import?Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-65452546374310467862015-09-07T15:14:58.695+02:002015-09-07T15:14:58.695+02:00Strange enough, I can not get to Rollback task als...Strange enough, I can not get to Rollback task also. If I enable "Begin Transaction Transname", the import block in not erroring out.Ljubinka Davidovska bloghttps://www.blogger.com/profile/05235846308489292027noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-56742543791422157732015-07-22T22:08:34.145+02:002015-07-22T22:08:34.145+02:00This trick works for one connection manager at a t...This trick works for one connection manager at a time. Also make sure you have set the RetainSameConnection to true.Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-57842430854638434642015-07-21T23:35:42.696+02:002015-07-21T23:35:42.696+02:00Hello- I have a similar setup but in addition to t...Hello- I have a similar setup but in addition to the above flows I also have a SQL task which inserts into an audit table before the first 'empty table' task and then updates the audit after the 'refill task' inside the same sequence container. I have 2 or more sequence containers in parallel like this doing different functional operations but the same audit inserts and updates. This does not work and gives an error as "[Execute SQL Task] Error: Executing the query "Rollback Transaction" failed with the following error: "Abort called not inside a transaction". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.".<br />I use different connections managers for Auditing SQL tasks and for other operations (like 'refill task' here). Audit table is in a Database1 and other operations target on a Database2.<br />Could you advice what could be going wrong? Should I be using DTC just because I am inserting into 2 different databases in the same package/sequence?<br />Thanks in advance.Anonymoushttps://www.blogger.com/profile/04501836851522408071noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-92074537200452941552014-01-17T08:10:14.747+01:002014-01-17T08:10:14.747+01:00Setting retainSameConnection to true is the most ...Setting retainSameConnection to true is the most important step (see 6), but thanks for the error message. Now everybody can google it :-)Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-83022059213870758872014-01-17T01:45:35.998+01:002014-01-17T01:45:35.998+01:00Hi - Just forgot to add, when you don't set re...Hi - Just forgot to add, when you don't set retainSameConnection == true, you get the error - [Execute SQL Task] Error: Executing the query "COMMIT TRAN MyTran" 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.<br />Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-3303496477569668802013-11-11T07:21:48.841+01:002013-11-11T07:21:48.841+01:00Joost - Your post saved me hours of pain, failure ...Joost - Your post saved me hours of pain, failure and frustration. As a rookie in SSIS and SS, I would have never imagined that there could be a simpler and neater approach. I hope that I can become an expert like you someday and make such great articles. Thank you very much indeed. You deserve a medal, from Obama himself. Check out the meme "give that man a medal". :)<br /><br />http://www.indianfunpic.com/wp-content/uploads/2013/07/Give-That-man-a-Medal.jpgAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-6405683872517943362013-01-08T10:29:00.284+01:002013-01-08T10:29:00.284+01:00Thank u , It really help me to solve my issue :)Thank u , It really help me to solve my issue :)Samithhttps://www.blogger.com/profile/03359088979541576655noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-11375018077986878712012-07-09T13:38:25.785+02:002012-07-09T13:38:25.785+02:00@Anonymous: You have to give me a little more deta...@Anonymous: You have to give me a little more details then just saying it isn't working. Contact me via the <a href="http://microsoft-ssis.blogspot.nl/p/contact-me.html" rel="nofollow">webform</a>Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-85392877717396226112012-07-09T13:14:40.147+02:002012-07-09T13:14:40.147+02:00i am following the same procedure as above but sti...i am following the same procedure as above but still not able to rollback on failureAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-37378445802729884442011-11-08T14:45:57.988+01:002011-11-08T14:45:57.988+01:00Did you connect the last Execute SQL Task to both ...Did you connect the last Execute SQL Task to both the Commit and the Rollback? Did you use a Logical OR instead of a Logical AND in the Precedence constraint?Joost van Rossumhttps://www.blogger.com/profile/01125981589974671317noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-86803299620973865492011-11-07T03:20:44.854+01:002011-11-07T03:20:44.854+01:00Hi,
If I want to have an Execute SQL Task after CO...Hi,<br />If I want to have an Execute SQL Task after COMMIT/ROLLBACK Tasks, can you please let me know how to go about. Though I tried using Execute SQL Task after the COMMIT/ROLLBACK Tasks , it does not proceeds further after COMMIT task. I changed the Precedence constraint to Success/Completion, but its not working. Any help is appreciated.Debasishhttps://www.blogger.com/profile/13883214937800853541noreply@blogger.comtag:blogger.com,1999:blog-2303058199815958946.post-81073150950417567552011-09-13T21:41:18.504+02:002011-09-13T21:41:18.504+02:00I think by not using named transaction scopes
E.g....I think by not using named transaction scopes<br />E.g. Begin Tran MyTran ...<br />Commit Tran MyTran / Rollback Tran MyTran<br />you are running into a risk of not being able to re-execute the package if it fails.<br />I think to recover you need dynamic tran names.<br />E.g. Begin Tran tr_232_232.<br />Can you verify if you can abort or fail the package in the middle so it does not reach one of the last steps and then restart it?Arthur Zubarevhttps://www.blogger.com/profile/01501752900846130379noreply@blogger.com