May 11, 2012 at 1:50 pm
Created a small package with one data flow that has multiple sources that are merge joined together through the flow.
We are a hosted business so my sources contain different servers, different clients and I use sorts to get server/client in line before joining (we have a custom component that "makes the rounds" of whatever servers I enable for that run to return the data).
The package worked perfectly until it didn't, which was in teh course of one day. What is now happening is that the sort after "the main records" runs to a certain point and stops. no error. package just hangs. And will stay that way all night (had to see...)
It will be the same record count thru the sort each time. If it weren't so reliably reproduceable, I might suspect bad data.
Fearing corruption, I first replaced the sort component and then completely remade the package. It is still occurring.
If I pull only one or two small clients, it works as it should.
I was wondering if it's possible that other sources getting their data and thru their sorts and ready to input to MJ before the "main records" get thru their sort could be the issue? I would love a sequence container in the data flow to test that, but, not an option. I would otherwise have to dump to temp tables to split into mult data flows which we don't want to do but maybe will haev to?
Any ideas welcome!
May 11, 2012 at 11:46 pm
SSIS sorts are heavy on RAM - have you checked the server's health at the point where the sort goes bad?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 14, 2012 at 11:47 am
That's teh thing - the server seems to barely notice that anything is running!!!
I decided to remove the sort from right after the main source query to see if it has to do with the other pieces "waiting around" and that seems to have made things go again.
But that also means I had to redesign to dump "Query 1" into a temp table to pull from in a new data flow. We're not keen on temp tables here, but it solves the problem.
I'll chalk this up to experience and remember that often a redesign can get me around the unexplained nuances in SSIS ...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply