October 31, 2008 at 2:46 pm
i am facing the same problem? when enable transaction on package level it hangs on the dataflow task. did you find any solution to your issues? if yes can you pass on to me please
Dost
November 3, 2008 at 7:59 am
Hi Dost,
It's all about transaction management or more to the point distributed transaction management. In our case, we were trying to do to much in the one package and hadn't paid enough attention to when we were opening transactions (setting the TransactionOption to 'Required') and when we were creating transactions within transactions (setting the TransactionOption to 'supported').
Say, for example, we have a data flow task with two components in it, CompA and CompB. At the base level in the data flow task (right click the yellow space) we set the TransactionOption to 'Required'. Then, we set both the component's TransactionOptions to 'supported'. The component will now join the distributed transaction of the data flow task. In itself, this doesn't sound like the end of the world.
Now, let's say CompA truncates TableA then the flow continues onto CompB which inserts into TableA. Slight problem here! The truncate has locked the table in the distributed transaction and won't give up the lock until the distributed transaction has completed... so CompB sits patiently and waits for the table to be free.... you're deadlocked!!
The only way out of this is to either restart the DTC service on the machine (although you may be able to terminate the SPID being blocked by -2, the DTC's SPID).
In theory, this transaction management will extend to all containers which are set to create new or join existing transactions. So if you have a master package calling child packages with lots of control flows, etc. and they're joining the master packages distributed transaction then... well... ouch.
Jamie Thompson wrote an excellent article about this:
http://www.sqlservercentral.com/articles/SQL+Server+2005+-+SSIS/transactionsinsqlserver2005integrationservices/1653/[/url]
Also useful is:
http://msdn.microsoft.com/en-us/library/ms137690.aspx
Hope this helps!
Martin
February 6, 2009 at 9:03 am
Hey guys it seems i have found the solution.
I was also facing the same problem but after after 2 dayz trail i found out that my Sequence Container i have kept Transaction = "Required" but for few data flow task (appear just below SQL Task) has Transaction = "Not Supported". I changed it to "Support" and every thing worked fined !!
Thanks for giving my path for R & D
February 9, 2009 at 2:35 am
Just try to comment out the lines begin transaction and commit transaction and just give a try.
February 9, 2009 at 2:45 am
Omhoge, this is the reply to your question.
I too did one SSIS, and had such a problem
But I deleted the catch block which i wrote in the sp and that solved my problem
http://www.sqlservercentral.com/Forums/Topic535078-149-1.aspx#bm537150
February 17, 2009 at 1:31 am
And also ... if the same problem happens with Script Task. And in script task your opening connection with OLEDB and have transaction enabled then your GONE !!! it will hange 🙂
Please use and also it is recommend in the script task component that your use SQL Connection since it support transaction. I below is the snipped of code for opening and closing the connections
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
MyBase.AcquireConnections(Transaction)
connMgr = Me.Connections.CDWConn
sqlConn = CType(connMgr.AcquireConnection(Transaction), SqlConnection)
End Sub
Public Overrides Sub ReleaseConnections()
MyBase.ReleaseConnections()
connMgr.ReleaseConnection(sqlConn)
End Sub
February 17, 2009 at 10:45 pm
Are you using Sequence Containers? If so, reduce the number of dataflow or other complex tasks inside the problematic container down to 2 or less. Alternatively, if you have an overly complex task, break it out into multiple tasks.
I was having the same problem. I was building a SSIS Package that uses a single Transaction for the package with multiple sequence containers each containing several SQL tasks and Data Flow tasks.
In Debug mode the package kept hanging with no error message. After much frustration I tried reducing the number of dataflow tasks in my sequence containers to 2. Debug now runs through cleanly! 🙂
February 19, 2009 at 7:28 am
For distributed transactions to work, you have to get DTC configured properly on ALL machines participating in the transaction. This includes your development workstations.
There is a free tool from Microsoft called DTCPing that can help debug DTC problems. It's not great but it's something.
You need to make sure the correct ports are open on the firewalls of participating machines, that DNS is configured correctly, and there are a number of security issues to consider.
Once you get DTC configured properly, transactions work great in SSIS.
March 9, 2009 at 10:34 pm
Had a very similar problem, and fixed it by setting the EngineThreads property on each task in the package to 2.
Hope it helps!
March 10, 2009 at 8:17 am
Interesting, thanks.
Where so you find EngineThreads? I didn't see them in the package properties.
appreciate it.
Skål - jh
June 12, 2009 at 2:22 am
I believe they're referring to SQL 2005 SP2.
Having installed SP2 but am still getting SSIS locking itself. Have just set the connection to RetainSameConnection and it seems to be working.
After having loaded the entire package, it is working fine.
RetainSameConnection=TRUE rocks!
March 8, 2010 at 10:43 am
Have you found a resolution to the problem yet?
March 8, 2010 at 10:45 am
any resolution to that problem yet?
March 8, 2010 at 10:45 am
any resolution to that problem yet?
March 17, 2010 at 4:46 pm
I had the same problem and I had to change the OLE DB destination ValidateExternalMetadata as suggested above. That solved the hanging problem. I also found that before the package would work again I had to change my OLE DB source to an ADO NET Source. That changed data types which caused a lot of work.
Viewing 15 posts - 31 through 45 (of 54 total)
You must be logged in to reply to this topic. Login to reply