SSIS Package hangs without any errors

  • 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

  • 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

  • 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

  • Just try to comment out the lines begin transaction and commit transaction and just give a try.

  • 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

  • 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

  • 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! 🙂

  • 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.

  • Had a very similar problem, and fixed it by setting the EngineThreads property on each task in the package to 2.

    Hope it helps!

  • Interesting, thanks.

    Where so you find EngineThreads? I didn't see them in the package properties.

    appreciate it.

    Skål - jh

  • 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!

  • Have you found a resolution to the problem yet?

  • any resolution to that problem yet?

  • any resolution to that problem yet?

  • 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