April 20, 2006 at 8:29 am
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
April 21, 2006 at 1:08 pm
[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
April 24, 2006 at 12:09 pm
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
April 26, 2006 at 1:57 pm
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
April 28, 2006 at 2:12 pm
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
May 2, 2006 at 3:01 pm
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
May 15, 2006 at 2:33 pm
Any further updates?
May 18, 2006 at 2:57 pm
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.
SQL Server MVP
Solid Quality Learning
June 13, 2006 at 8:07 am
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
June 15, 2006 at 2:23 pm
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
July 6, 2006 at 2:37 pm
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
February 28, 2008 at 7:31 am
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.
February 29, 2008 at 8:52 am
Omhoge,
were you able to find a solution ?
February 29, 2008 at 9:23 am
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.
February 29, 2008 at 11:16 am
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
Viewing 15 posts - 1 through 15 (of 54 total)
You must be logged in to reply to this topic. Login to reply