Problem
A large SSIS package, scheduled as a SQL server job, that needs to import tens of large Excel files into a datawarehouse, always halted with errors out of resources or memory on a SQL 2008 R2. We noticed that on the file server only 2 or 3 of the excel files were handled, and moved over to another folder, before the package halted.
Cause
After inspection of the way the package was constructed, I noticed that several data flow tasks were not interconnected to run in serial, so they ran in parallel, because the dataflow tasks do not depend on each other.
Solution
In Visual Studio 2008 we connected each data flow task to the next so that all data flow tasks ran in serial, not in parallel. After installing the package on the server, we launched the job and this time the job ran fine. Although it still ate large amounts of CPU it kept on going. Of course I will have to monitor this for the next days to see if the job runs properly from now on.
Conclusion
Parallel processing may sound fine, but I doubt that SSIS has enough intelligence to handle things properly, it looks like it starts all the flows at once and then chokes in all parallel threads. Therefore if there is no need for a parallel processing just let the flows work in serial. If one really wanted parallel processing, it would probably be better to split the package in different packages, schedule those to start at the same time and then let the operating system handle the parallel processing. But then your maintenance of the packages is more of a burden in comparison to whatever time you may gain over serial processing.
2 comments:
louis vuitton outlet, sac longchamp, ralph lauren pas cher, replica watches, louboutin outlet, louboutin shoes, christian louboutin outlet, prada outlet, ugg boots, longchamp pas cher, tiffany and co, louis vuitton, louboutin, air jordan pas cher, tory burch outlet, cheap oakley sunglasses, nike outlet, polo ralph lauren outlet, ray ban sunglasses, polo ralph lauren outlet, louis vuitton outlet, nike air max, replica watches, louis vuitton, air max, longchamp outlet, michael kors, oakley sunglasses, chanel handbags, nike free, nike roshe run, oakley sunglasses, burberry, tiffany jewelry, ray ban sunglasses, kate spade outlet, prada handbags, oakley sunglasses, nike air max, louis vuitton, louboutin pas cher, uggs on sale, ray ban sunglasses, oakley sunglasses, longchamp, jordan shoes, gucci outlet, ugg boots, nike free, longchamp outlet
lancel, celine handbags, jimmy choo shoes, bottega veneta, nike roshe, asics running shoes, gucci, giuseppe zanotti, nike huarache, mcm handbags, herve leger, hollister, hollister, new balance, ray ban, p90x workout, soccer shoes, babyliss, longchamp, mac cosmetics, nike trainers, chi flat iron, mont blanc, vans, vans shoes, ghd, iphone cases, nike air max, hollister, ferragamo shoes, ralph lauren, louboutin, nike air max, beats by dre, valentino shoes, converse outlet, lululemon, north face outlet, instyler, soccer jerseys, birkin bag, insanity workout, baseball bats, north face outlet, abercrombie and fitch, timberland boots, reebok shoes, nfl jerseys, oakley, wedding dresses
Post a Comment