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.