SSIS Package/Agent Job Not Failing!!!

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    I have a package running in SQL 2016 using Project Deployment. One of the steps executes a stored procedure.  All of the tasks have their error parameters left at default so any error should cause the package to fail because the maximum error count is 1.

    In this particular instance the database user account used for the connection manager did not have Execute permission for the stored procedure so the Execution failed, but the SQL Agent Job reported success even though it is set to report failure if the agent job step fails.

    I checked the Execution Report in the SSIS Catalog. The overall status shows as success, but when I go into the details in shows the failure.

    Is this a bug in SSIS or am I missing something?

  • Thom A

    SSC Guru

    Points: 98255

    Maybe you have the package's ForceExecutionResult Property has been set to Success?

    • This reply was modified 1 month ago by  Thom A.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    No I checked that, the tasks ForceExecutionResult property is set to None. It's not something I ever change.

  • Tim Mitchell

    SSCoach

    Points: 15652

    How are you executing the package? Is it running as a T-SQL job step, or are you using the SSIS Package job step type in the SQL Server Agent job?

    Tim Mitchell, Microsoft Data Platform MVP
    Data Warehouse and ETL Consultant
    TimMitchell.net | @Tim_Mitchell | Tyleris.com
    ETL Best Practices

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    I am using an SQL Agent Job that I am running manually during the testing stages. I have used SSIS for years and never had this problem before. 2 other packages in the same project have now also show the same behaviour when testing them. This time the stored procedures did not exist that the packages are calling, as they were missing in the database release. Both of these packages report success at the top level but show the error when you go into the execution report details. Again in these packages each object has the Max Error Count set to 1 so they should fail.

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    I set the FailParentOnFailure property to True for the Execeute SQL Task, it made no difference.

    I also subsequently tried FailPackageOnFailure = True and that also made no difference. In Both cases the execution report shows success but with a Task Failure in the details!!!

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    I found the problem. About a week ago the SQL Services failed to start. The server in question uses the default SQL service accounts and someone updated a Group Policy blocking all permissions for these accounts. To get up an running as quickly as possible we changed the Log on for the service accounts to Local System. However the Owner of the SQL Agent jobs is the default NT Service\SQLSERVERAGENT account. This was enough to stop the error bubbling up. I changed the Job Owner to my domain account and Bingo! I now have a failure.

    The department that messed up the permissions in the Group Policy is investigating. It's been a few days now, how difficult is it to role back a Group Policy change?

    My guess is they will set up a dedicated AD account per server for the SQL services but then stop Kerberos Authentication from working!

    • This reply was modified 3 weeks, 5 days ago by  tim.ffitch 25252. Reason: Typing errors
  • Sue_H

    SSC Guru

    Points: 89970

    My guess is they could roll back the policy but they can't set the permissions back to how they were originally. You could change the accounts using SQL Server Configuration Manager and the permissions should get set correctly but it depends on what they did with the group policy and there could be a chance the changes would just be blocked. So in that case, give that group the documentation and tell them to figure out how to fix it. They should be the ones worrying about it, not you. The permissions needed for the different accounts are listed in the following:

    Configure Windows Service Accounts and Permissions

    Sue

     

  • tim.ffitch 25252

    SSCommitted

    Points: 1989

    Thanks' for the info Sue, it's been a while since I looked at Service Account permissions.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply