SSIS Package Not working RRS feed

  • Question

  • Hi

    We have SSIS Package in which all the tasks in control flow are connected with "Completion Precedence".

    Ideally it should execute all the tasks irrespective of failed or successful execution of any of the task. And this works as expected from BIDS.

    But when package is scheduled from SQL Server Agent job (Single step), it doesn't execute next task (Execute SQL Task) if there is failure with any of the task before that (Execute SQL task) even though they are connected with Completion precedence.

    We have several Execute Package tasks which call child packages to load the data and all are executed in parallel.  There is one Execute Sql task at the end of one of the Execute Package task (Connected using completion precedence) which calls stored procedure to do some cleansing activities.

    Friday, September 5, 2014 4:48 AM

All replies

  • Whats the error it gives when you run it from the sql agent job?

    Also are you trying to execute package from file system inside job or have you deployed package to msdb / catalog?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, September 5, 2014 5:30 AM
  • Running the Job from file system only. And it gives error for failed tasks only like if there are any issues with files then it gives error for that.

    Due to this failed task, next task is not being executed for which errors are not getting logged. Again when not executed task is run manually then it runs successfully.

    Friday, September 5, 2014 5:41 AM
  • You can enable logging on this package to see what errors ssis logs on this. Also check the sql agent job history to see the errors logged.

    Thanks, hsbal

    • Proposed as answer by Elvis Long Tuesday, September 9, 2014 9:44 AM
    • Unproposed as answer by Elvis Long Tuesday, September 9, 2014 9:44 AM
    Friday, September 5, 2014 7:17 PM
  • Sounds to me like you need to adjust the MaximumErrorCount property for your task(s). If its set to one, and encounters an error, it will bomb the package. If its set to two, and encounters one error, it will continue.

    Friday, September 5, 2014 7:22 PM
  • I have now enabled the log and it seems that Execute SQL Task was executed but it didn't take any time to execute. Job started at  5:44:42 AM and ended at  5:44:42 AM. It logged the event Preexecute and PostExecute both at the same time 5:44:42 AM but data was not updated through the SP but no Error event logged. Again when I ran SP anually it worked fine and updated data. SP Takes somewhere around 2 mins to execute.
    Wednesday, September 10, 2014 11:45 AM