none
SSIS package hangs randomly on different tasks when scheduled

    Frage

  • 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

    prashb

    Donnerstag, 29. September 2016 23:36

Alle Antworten

  • Hi PXBB,

    What package exactly do ?

    how many ram assign to mssql server ?

    Need to monitor SSIS package

    http://stackoverflow.com/questions/15004109/can-you-monitor-the-execution-of-an-ssis-package-in-bids-as-it-runs-on-the-ser

    https://msdn.microsoft.com/en-us/library/aa337088.aspx

    debug SSIS using Data viewer

    https://www.simple-talk.com/sql/ssis/debugging-data-flow-in-sql-server-integration-services/


    Please click Mark As Answer if my post helped.

    Freitag, 30. September 2016 02:02
  • Hi Dinesh,

    The package loads data from sql server to salesforce using SSIS and in data flow it uses taskfactory add on 

    to laod data to salesforce. The RAM size is 128 GB. As per the stackoverflow link you provided.I do see at which task it is stuck or hanged by going to ssis catlog in management studio ->rightclick on package ->Reports-Standard reports->Allexecutions. Also this does not happen every time that I can debug using data viewer.When this package is scheduled in SQL Agent. Once in 2 days it happens .No specific time orday.


    prashb

    Freitag, 30. September 2016 07:02
  • Hi PXBB,

    Have you checked this issue is occurring on a specific Data Flow Task or random on all Data Flow Tasks?

    In this case, if possible please try to split these DFTs to multiple packages, then schedule these package in SQL Agent Job with multiple steps. Then test whether this issue will occur again.

    In addition, I also find a similar case that introduces SSIS Data Flow Task hangs on execution of Pre-execute phase may be caused by validation for CONNECTIONS. Please take a reference.

    Regards,

    Seif


    Regards, Seif

    Freitag, 30. September 2016 10:59
  • Thanks Seif. This occurs at random on any of the DFT and next time it occurs it may not be the same Data Flow task. I have delayed validation to true on all connections.Prior tasks succeed and then at random other DFT task hang.Thats what the sql server Reports log say.

    prashb

    Freitag, 30. September 2016 20:22
  • I would suggest that you investigate what the locking issue is. You have 7 hours to do this, so it should be easy.

    Use sp_who2 to see what SPID your task is waiting on.

    Use dbcc inputbuffer() to see what the blocking SPID is doing.

    Use sp_lock to see what the blocked resource is.

    Hope that helps,


    Richard

    Mittwoch, 12. Oktober 2016 23:20
  • Thank you so much Richard. We found the root cause of the problem. This would randomly happen at sql server destination or source for various data flow tasks .Please see the attached images.  When we killed the package and ran it again. It went fine. We are using Task factory from pragmatic works to select,upsert data from salesforce and sql server destination. In 3rd picture I have included the screenshot of properties of the affected destination of the SSIS package. Do you recommend any update to its properties ?.


    prashb

    Donnerstag, 13. Oktober 2016 23:56
  • It's also possible that the TF Upsert Destination 1 is hanging, randomly, with TF Upsert Destination. sp_who2 and sp_lock will help you determine where the locking is happening.

    What you might try and do is, update this data flow task to copy data to a staging area (truncate table before data flow). Then use SQL tasks with MERGE to update/insert into your destination tables. To avoid deadlocks, serialise these tasks. If necessary explicitly lock the destination tables. ie

    BEGIN TRAN

      SELECT TOP 0 * FROM MyTable WITH (TABLOCKX)

      MERGE ......

    COMMIT TRAN

    Hope that helps


    Richard

    Sonntag, 16. Oktober 2016 22:51
  • FYI - TaskFactory is now part of SentryOne.  See sentryone.com/task-factory
    Mittwoch, 20. Juni 2018 15:32