SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS Package hangs without any errors


SSIS Package hangs without any errors

Author
Message
Vince Napoli-320548
Vince Napoli-320548
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 132

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


Elmer Miller
Elmer Miller
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1021 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.
JackTheC
JackTheC
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 95
Omhoge,

were you able to find a solution ?
Elmer Miller
Elmer Miller
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1021 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.
omhoge
omhoge
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 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
Elmer Miller
Elmer Miller
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1021 Visits: 250
Apparently SP2 fixes the problem. I haven't had any more hangs since applying SP2!
omhoge
omhoge
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 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
damien.alvarado
damien.alvarado
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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.
omhoge
omhoge
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 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
Roberts-758379
Roberts-758379
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search