March 6, 2013 at 12:06 pm
I'll start off with an apology for rather vague subject line but I can't think of any other way to describe it.
We have an SSIS package running on a SQL 2008 R2 instance that is populating a data warehouse. It is been working fine on several other instance until a few days ago.
There is a data flow task using the OLE DB Source control extracting data from a view using the option Table or View option for the data access command. This view generates about 2000 rows and normally run in a few seconds. The package is hanging on this task which never completes but continues to execute collecting an excessive number of reads. After letting it run for more than 14 hours, it never finished but the connection showed more than 1,000,000,000 reads and growing so whatever is happening was still active.
The weird thing is the view works fine through SSMS. The weirder thing is if I change the data access mode for the control to SQL Command and add a query that just SELECT * FROM view, it also runs without any problem at all.
I have dropped and recreated the view, checked permissions and isolation level. I also compared the properties of the control with other that also select from views but aren't having the same trouble. The end result was I found nothing to tell what the problem.
While changing to a query resolves the problem, I still have no idea why there was a problem in the first plans. I hate mysteries that are repeatable but have workarounds because I never know if the underlying problem will return without warning.
Any comments or thoughts about this issue would be much appreciated.
March 6, 2013 at 12:32 pm
Offhand, I have no idea and you're dealing with some really idiosyncratic glitch. I'm relatively sure you won't know for sure unless you call Microsoft and they look into what the component is doing directly.
That said, I've seen it before, but only once, and that was 2k5. I used the same method to avoid it, simply using a T-SQL call to force it to read the view properly, and including the NOEXPAND hints as necessary.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply