CODEWITHSUNDEEP
sql-serverssis
ARKTech
ARKTech

Reputation: 11

SSIS Package Failed to Validate from SSISDB Integrations Catalog

On our production server SSIS packages failing from 24th Jan. They used to run fine (in default runtime) until 23rd Jan 2022.

Some reason they execute and validate fine if I run in 32 bit runtime but fails in default runtime.

Environment - SQL Server 2016 Standard Edition.

When a package executes first it fails and reports the following execution status in SQL Agent Job history.

Executed as user: . Microsoft (R) SQL Server Execute Package Utility Version 13.0.5888.11 for 64-bit Copyright (C) 2016 Microsoft. All rights reserved. Started: 11:41:23 Package execution on IS Server failed. Execution ID: 197521, Execution Status:5. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 11:41:23 Finished: 11:41:26 Elapsed: 2.235 seconds. The package execution failed. The step failed.

In SSISDB Catalog execution report there are no details of the issue reported but the status is set to Pending Execution.

https://i.sstatic.net/9GCFi.png

When validating the package in SSISDB catalog the following error message is displayed.

A .NET Framework error occurred during execution of user-defined routine or aggregate "validate_package_internal": System.ComponentModel.Win32Exception: Access is denied System.ComponentModel.Win32Exception: at Microsoft.SqlServer.IntegrationServices.Server.ISServerProcess.StartProcess(Boolean bSuspendThread) at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.ValidatePackageInternal(SqlInt64 projectId, SqlInt64 package_id, SqlInt64 versionId, SqlInt64 validationId, SqlString targetEnvironment, SqlInt16 use32BitRuntime) . (.Net SqlClient Data Provider)

We have performed the following troubleshooting steps to resolve the issue but no luck.

  1. Restarted the server
  2. Applied Windows updates
  3. Applied additional group policy permissions to the user accounts that are running SQL Server, Integration Service and SQL Agent services as per the Microsoft documentation.
  4. Uninstalled and reinstalled SQL Server 2016 with Integration Services
  5. Deleted and recreated SSISDB catalog

I cannot see anything online related this therefore its proving to be hard to find the problem. Can you please shed some light on this.

Upvotes: 1

Views: 2286

Answers (1)

JohnH
JohnH

Reputation: 2133

This phrase jumps out from the error message generated after validating the package in the SSISDB catalog.

  • Access is denied

Security changes appear to be needed to enable the required access.

Upvotes: 0

Related Questions