Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

SSIS Package hangs without any errors Expand / Collapse
Author
Message
Posted Thursday, July 6, 2006 2:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 3:16 PM
Points: 5, Visits: 114

Look at whether or not you have triggered a locking issue on the database.   I have caused SSIS to hang up / deadlock in a single data flow in my production packages.   The tasks in the data flow would be all yellow in BIDS, but no further activity in the package as the activity is blocked on the database.   You can see the locking issue with sp_who2 and sp_lock on the data base very easily.   I had a data flow with an OLE DB source, Lookup transform to determine if a record exists, an OLE DB destination for inserts, and OLE DB command for updates.   The package execution had to be cancelled and fast load option removed in the OLE DB destination. 

 

- Vince Napoli

Post #292642
Posted Thursday, February 28, 2008 7:31 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 9:25 AM
Points: 977, Visits: 250
I also have a package that inexplicably hangs (on 64 bit server). It hangs intermittently even in the designer. My package is simple, with one dataflow that reads data from several sql tables and updates several other tables. I have only been able to catch it hanging in the designer when there is no data in the pipeline. So if one of my data sources has no rows and I try to do a merge joins with data from another table, it might hang as if waiting for input. Since this condition happens a lot, my partial workaround is to check the rowcount on the tables before executing the dataflow and not execute it if there is now data to prevent it from hanging. Even this apparently doesn't always work, but I haven't been able to catch this type of failure in the desginer.
If no one knows how to solve this, perhaps someone knows how to have a package timeout. If I could get the package to timeout or fail, then I could re-run it and possibly get it to succeed. As it is I have to manually go in an stop the scheduled job that is stuck and restart it.
Any advice might be helpful. Thanks.
Post #461636
Posted Friday, February 29, 2008 8:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 13, 2014 8:40 AM
Points: 4, Visits: 95
Omhoge,

were you able to find a solution ?
Post #462450
Posted Friday, February 29, 2008 9:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 9:25 AM
Points: 977, Visits: 250
Omhoge, Do you have SP2 installed?
In my case, I don't have SP2 installed yet. I plan to install it this weekend. Then I will test to see if the problem has been fixed.
Post #462498
Posted Friday, February 29, 2008 11:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 6, 2012 9:12 AM
Points: 500, Visits: 183
There were no lookups involved in the packages I was working on at the time.
No deadlocks were shown in sp_who2 when the packages hung.
I could not find any thing in the SQL server itself that explained it.

That first master/multiple child package model was not successful in time so that was scrapped for that particular project and we went with a pure TSQL ETL process.

In the simplier ones since then, I had to avoid using distributed transactions, and used SQL transactions with a common connection held throughout the package so it could roll back .

This is going back a bit, I do not think SP2 was installed at the time.

I never found the root cause for for the hanging.
Hope that helps.



Skål - jh
Post #462589
Posted Monday, March 3, 2008 9:47 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, November 12, 2013 9:25 AM
Points: 977, Visits: 250
Apparently SP2 fixes the problem. I haven't had any more hangs since applying SP2!
Post #463245
Posted Monday, March 3, 2008 11:58 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 6, 2012 9:12 AM
Points: 500, Visits: 183
That's good news at least.
Unfortunately, we had to put SP2 on hold here, we hit a bug where the jobs stopped running due to a profile issue,
on our DEV box where we tested out the service pack.
Apparently there's a hot fix for that but it's still being tested by the DBA-s.

But at least we have some hope for this issue if it is fixed in SP2.

Thanks E.M.


Skål - jh
Post #463340
Posted Monday, May 19, 2008 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 4:05 PM
Points: 6, Visits: 64
In the Connection Manager for your SQL Server you must set RetainSameConnection=True. This is what we did in our project, but we could not get it to work for our Oracle destination tables. Worked fine for SQL Server.
Post #503083
Posted Wednesday, May 21, 2008 1:05 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, July 6, 2012 9:12 AM
Points: 500, Visits: 183
Yes, we used RetainSameConnection=True in the Connection Manager also, and it worked for the transactions.
We had to stop using DTC altogether.
Thanks all!


Skål - jh
Post #504773
Posted Sunday, May 25, 2008 10:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2008 12:16 PM
Points: 2, Visits: 11
I've got the same "hanging yellow" symptoms, but none of the solutions have worked. There's a mention of "SP2", but it's not clear to me which app requires SP2. Is it for BIDS? SQL Server 2005? .NET Framework 2.0?
Post #506336
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse