asked
Etl Processing Error
Hi, ETL Packages are getting failed sometimes(Package Execution Error). Eventhough executing ETL Package again from start, getting the same Error. But after Restarting Sql Service in BI Server, it is working fine. Whether it is the issue from Developer Code side or from server side. Is there any comments on this, really would be appreciable. Please advice on the same to clear this error in Future.
Add error handling to the packages to output errors to a destination file or db when errors occur. Or if your using SSIS Catalogs use the execution report to drill into the errors Or if they are scheduled get the agent to dump the full output to file. Without the error messages we cant see whats wrong.
Actually While ETL Processing at night, some Control sp(Stg+Fact sp's) get struck with the suspended status in the back end(SP_WHO5), and its not getting completed. We couldn't locate exact issue for that suspended status. But after restart the Sql Service in Db, we can able to go through the execution of SP'S. Let me know the possible types of error for this scenario?
What have you checked so far? What is the type of wait causing the suspension?
Hi Antony.green, Thanks for your reply and i am struck with suspended status with wait_type sometimes like "lck_m_u" or "cxpacket". You've any idea on that?
The wait type of LCK_M_U is where the database engine is waiting to acquire an UPDATE lock. If you have a highly transactional system that you are writing to, you may be having contention that you'd have to address. Reference:
http://blog.sqlauthority.com/2011/02/15/sql-server-lck_m_xxx-wait-type-day-15-of-28/ The CXPACKET is occurring because the query is being parallelized by the query engine. What is the MAXDOP set on the instance? Reference:
http://blog.sqlauthority.com/2010/11/13/sql-server-reducing-cxpacket-wait-stats-for-high-transactional-database/ The suspended state is most likely because of things gather information as data is processed through the package. I would suspect that if you watched, it would rotate from SUSPENDED to RUNNING as more data is processed. I could be wrong however. As others have stated, seeing the error would be the most beneficial. Just curious, have you just let the package run to see if it ever completes?
I am assuming you are using Microsoft ssis and sql job. It would be worthy to export the package and run it through BIDS. In this manner you would be able to check exactly where the error occurs.
21 People are following this question.
