July 18, 2019 at 10:40 am
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?
July 18, 2019 at 11:07 am
No I checked that, the tasks ForceExecutionResult property is set to None. It's not something I ever change.
July 18, 2019 at 9:34 pm
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
July 19, 2019 at 12:54 pm
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.
July 19, 2019 at 2:52 pm
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!!!
July 19, 2019 at 3:32 pm
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!
July 19, 2019 at 4:47 pm
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
July 22, 2019 at 9:40 am
Thanks' for the info Sue, it's been a while since I looked at Service Account permissions.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply