0

My packages have been running on our production environment for a couple of weeks until they started failing with very little information apart from packages having a pending execution status.

All packages fail regardless of the user executing them e.g. myself or the SQL Agent service account.

There is a cryptic error message in the windows log about activation permissions for the SQL Integration Services. Setting permissions for the accounts calling the package makes no difference,

I managed to get them working by repairing the SQL Server installation I couldn't see what repairs it had made.

Now following a reboot of the server all packages refuse to run. I have managed to get them to work by repairing the SQL Server installation again. This is obviously not a scalable solution.

I suspect that some group policy setting is overriding a local security setting and this is why it happens after some time or after a reboot.

Has anybody come across this issue before or have any ideas where to look.

  • have you checked the 'All Executions' report against the catalogue in Integration Services Catalogs? This may provide you with more specific information on what is causing problems. – DimUser Mar 26 '18 at 13:23
  • @DimUser Yes I have checked the all executions report and it shows no messages, the report is blank – Steve Mar 27 '18 at 9:56
  • then potentially that suggests that the packages aren't even getting to execution stage. Does the account for SSIS service have the appropriate permissions, or is this still a local service account? Have you tried running a package in debug mode and checking the executions logs? – DimUser Mar 27 '18 at 10:31
  • @DimUser as I said in my question they run then after some days they stop working and the only way i have managed to fix them is by running a repair. They run under NT Service\SQLAgent$InstanceName – Steve Mar 27 '18 at 12:38
1

If the packages are running as part of a SQLAgent job, check the job for what user it's running as. Check those permissions, and that could be your problem. I just had to repair a situation like that. Permissions got yanked from the wrong users, and it broke everything...

  • The job is running as the NT Service\SQLAgent$InstanceName account. The package needs no access to other servers. Repairing the SQL Server installation fixes the issue for a while. The Agent account is specified as SSIS user – Steve Mar 27 '18 at 9:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.