September 29, 2016 at 4:50 pm
Hi All, I am using Visual Studio 2010, version 10.0.40219.1 SP1Rel. Our SSIS package has serially linked Dataflow tasks.Pulling data from sql server (version Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
using queries or some use stored procedures and load data to salesforce (task factory by pragmatic works). Our packages are scheduled every minute using SQl Server agent and packages located in sql server catalog.Sometimes the ssis package gets stuck or hangs at one of the Data flow tasks in pre execute stage or post execute for very longtime (can be 6-7 hours and does not complete).If we kill the job and rerun, it completes in few minutes.The server on which our ssis packages run have 128 GB ram.THis does not happen everyday. We have to periodically check if the package is not running since more than 6 minutes that is the average time for pkg to complete. What might be the reason for this random hanging at various tasks .When we see the reports of execution logs it does not say if the problem is at the source or destination
Some of the package settings I would like to share
- not using parent child pkg
- no checkpoint
- MAxconcurrentexecutables -1
- Delayed validation to true with Data Flow task
- Default Buffer Max rows 10000
- Default Buffer size 10485760
- Engine Threads 10
September 30, 2016 at 5:56 am
I'd check for locking/blocking.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
December 2, 2016 at 6:01 am
Nilesh,
We are experiencing a similar problem. Did you ever find the culprit? Was there a solution?
Thank you,
-Will
December 2, 2016 at 10:14 am
It is the destination that is causing the problem. I posted this on microsoft forum and got the answer.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply