Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Weird Behavior in Data Flow


Weird Behavior in Data Flow

Author
Message
dlander525
dlander525
SSC Rookie
SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)SSC Rookie (30 reputation)

Group: General Forum Members
Points: 30 Visits: 876
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.
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5709 Visits: 7660
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.


- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
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