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, April 20, 2006 8:29 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 06, 2012 9:12 AM
Points: 500, Visits: 183

Hello all,

The package I've been working on just hangs now without any errors in the execute process window or the application event log; running by either right clicking the master package and using "execute package" as well as double clicking on the master file and executing from the execute package utility. It just hangs, no red boxes just yellow and green ones if they change at all, no errors logged anywhere that I've found.

It has a three level hierarchy: master calls four (with more to come) packages each in a sequence container to use DTC, without any precedences for max. parallel execution, those four use another four packages which encapsulate repeated tasks writing to or updating common related tables. All packages use the same two DB connection managers for the local source and target databases.

The original all TSQL version of the same imports using a master and child sprocs ran without any deadlocks or hanging.

Because it's in SSIS now I thought it might be a new deadlock issue and have found checking deadlocks is a lot more complicated in SQL 2005 (having to export a deadlock grid xml from profiler and all that poop) but the xml file is empty and no deadlock events are being captured at all from what I can tell.

Anyone else get stuck in this corner?  I searched here but didn't find anything hope this is not a re-post. Thanks all, this forum is a live saver!



Skål - jh
Post #274200
Posted Friday, April 21, 2006 1:08 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 06, 2012 9:12 AM
Points: 500, Visits: 183
[lost post] Had set master package to delay validation, did not fix problem.

(actually 4/24 ended up here in 4/21)Well as a long shot I thought maybe it had something to do with the integrated source control. So I tried checking out the master package first, it ran once all the way through, now it just hangs again.

The point at which it hangs seems to be different each time but is usually after validation has started on at least three of the packages (those boxes turn yellow). I cannot run the package at all now from the file system by double clicking it, it throws a file not found exception, but the debugger finds nothing.

Sill open to any insights anyone has... Please.

MSDNno search results for"SSIS hanging", just one forum entry about virtual memory for 64 bit versions.

thanks all!


Skål - jh
Post #274619
Posted Monday, April 24, 2006 12:09 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 06, 2012 9:12 AM
Points: 500, Visits: 183
Well now I tried turning off logging in all the packages except the master one thinking it might be some sort of deadlock there. No effect.

------------ Update:Just tried setting ALL the packages to delay validation, still no luck. Master package will run all the way once in a great while but usually just hangs.


Skål - jh
Post #274966
Posted Wednesday, April 26, 2006 1:57 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 06, 2012 9:12 AM
Points: 500, Visits: 183

Well now I tried giving all the smaller sub packages which are called  the most their own OLE DB connection managers.  It was hopeful, but only worked a couple times through. Now either  the whole thing is hanging again no errors, no apparent deadlocks, or subpackages that worked one time fail with SQL errors that did not happen the last time.  poo!

 



Skål - jh
Post #275684
Posted Friday, April 28, 2006 2:12 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 06, 2012 9:12 AM
Points: 500, Visits: 183
Today I tried setting all the SQL tasks to Read Uncommitted but that didn't help either. I'm holding off on resorting to putting presidences between all the execute package calls in the master package as this basically kills the parallel processing the team is after. The random-ness of the hanging or failure of SQL tasks that work one time and not the next is strange.

Skål - jh
Post #276396
Posted Tuesday, May 02, 2006 3:01 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 06, 2012 9:12 AM
Points: 500, Visits: 183

It has something to do with distributed transactions.  Not sure why it's hanging like this but I'm taking them out.



Skål - jh
Post #276991
Posted Monday, May 15, 2006 2:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, August 20, 2007 5:51 AM
Points: 99, Visits: 1
Any further updates?
Post #280183
Posted Thursday, May 18, 2006 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 26, 2008 8:52 PM
Points: 7, Visits: 5
Are you using lookup's or other transformations that are caching data? Take a look at your output window to see what the package is doing right before it hangs up. Also monitor your perfmon SSIS data flow counters for memory and caching. I suspect that you are running out of memory for private caches. I have seen similar behavior before. If you are doing any sort of lookup against a large reference set, then you are probably running out of memory.



Rushabh Mehta

SQL Server MVP
Solid Quality Learning
Post #281248
Posted Tuesday, June 13, 2006 8:07 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 06, 2012 9:12 AM
Points: 500, Visits: 183
Well, it started happening again with a much smaller simpler package (we stopped using SSIS for the primary SQL ETL mentioned before).

This one is a straight forward text file import.

I added Transactions = Requred atthe package level.It's worked fine for a couple days and this morning it started hanging again just like the other big one. Gets to the data flow task, turns yellow and just stops and hangs till I kill it. When I remove Transactions and set it to Supported it runs.

Maybe we just can't use the Distributed Trans. Coord., have to see how to wrap the data flow in a SQL Transaction.


Skål - jh
Post #287035
Posted Thursday, June 15, 2006 2:23 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 06, 2012 9:12 AM
Points: 500, Visits: 183

Since DTC won't work for whatever reason, I tried using native SQL Server Transactions.

I followed the simple example from the Wrox book, the only clear mention of it I could find.

I added a SQL task at the head of the chain or tasks, "Begin Tran etlBlah"

then at the end of the chain of tasks added a SQL Task, "Commit Tran etlBlah".

But the commit SQL Task errors with "The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

Please help, how do you implement SQL Transaction support?  Adding DTC to even the simplest package eventually causes it to hang without any errors, native SQL transactions seem to have some sort of disconnect and cannot commit.



Skål - jh
Post #287895
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse