How to solve an SSIS Deployment Error

by Pio Balistoy on Apr 16, 2019 12:00:00 AM

Problem:

You are deploying a SQL Server Integration Services Package to the SSIS Catalog and it keeps on failing with the following error:

The error seems daunting but the main information to take away is: The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.

Cause:

You receive the main error when either your server is actually running out of resources and the external assemblies or modules (like CLRs and external stored procedures) could not run because there is no more memory/cpu available. But more often than not, it is because those external calls were not registered properly or marked as safe. SSIS package deployment calls a number of external assemblies during deployment of packages. Since these packages are saved inside the SSISDB database, the database must be set to "Trustworthy" or else external calls will fail. The Trustworthy property of the database lets SQL Server know whether it can trust the contents of the databases and its calls. By default, the property is off for any databases except msdb. You can understand more about this property here.

Fix:

To address this specific error, you'll need to set the Trustworthy property of SSISDB to ON:

select name,is_trustworthy_on from sys.databases where name= 'SSISDB' ALTER DATABASE SSISDB SET TRUSTWORTHY ON; select name,is_trustworthy_on from sys.databases where name= 'SSISDB'

Applies To:

SSIS Catalog has been around since SQL Server 2012.

Note:

If you're receiving this after you have restored SSISDB from backup, especially if that backup is from another server, you might encounter more issues. There are a number of additional steps that need to be done when restoring SSISDB database backup from another server. You can look here for the proper steps to take for restoring SSISDB to another server.

How to solve an SSIS Deployment Error

Problem:

Cause:

Fix:

Applies To:

Note:

No Comments Yet

Subscribe by email

Share this

How to solve an SSIS Deployment Error

Problem:

Cause:

Fix:

Applies To:

Note:

Share this

No Comments Yet

Subscribe by email