none
Question about SSIS Job execution /Job scheduling RRS feed

  • Question

  • Hi,
    I have a SSIS job schduled.It has some 20 SSIS packages, running serially.

    The problem is sometimes, due to dead-lock if some of the package for stuck, then the subsequent jobs are not running. Can we do some settings in the job scheduler something like ' if this package  is not comleted within 3 hours' , mark it as failure and move to next package?
    Monday, August 28, 2017 3:01 PM

All replies

  • HI Manesar,

    I will suggest you sequence conatiner there you need call and check condtion where packages is not completed within 3 hours.

    http://sqlblog.com/blogs/rushabh_mehta/archive/2008/04/24/gracefully-handing-task-error-in-ssis-package.aspx


    Mssql installation on Centos

    Monday, August 28, 2017 4:57 PM
  • The best solution is probably to resolve the issue causing the deadlock...

    I go here any time I have deadlock issues in my Servers:
    Handling Deadlocks in SQL Server


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    • Edited by KevinBHill Monday, August 28, 2017 6:16 PM added link
    Monday, August 28, 2017 5:01 PM
  • Kevin suggested right, we need enable trace flag which record in error log and check deadlock information in errorlog.

    DBCC TRACEON (1204, -1)
    DBCC TRACEON (1222, -1)


    Mssql installation on Centos

    Monday, August 28, 2017 5:11 PM
  • Hi Manesar,

    So, do you have 20 steps in one SQL Agent job? What's the meaning of dead-lock here? Is it SQL Server dead-lock?

    Anyone, I agree with KevinBHill, the best solution is to resolve the dead lock issue.

    And, from the perspective of SQL Agent job, it's hard to implement the logical, you are just able to set the next step on success or failure. Or you could try to use T-SQL to force a step failure based on the time.

    In terms of SSIS, you could try to call all 20 packages in one primary package, then use precedence constraint to control the flow, but why do you need to run serially? Is there any reason? This will take long time to execute the all packages completely. Personally, you could execute the packages in parallel with multiple threads if possible.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 29, 2017 7:44 AM
    Moderator