How to solve an SSIS Deployment Error

Posted in: Microsoft SQL Server, Technical Track
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.

 

 

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Lead Microsoft Data Platform Consultant
Pio Balistoy is a Microsoft MVP for Data Platform from Singapore. He has been a Database professional for more than 17 years. He brings his passion for SQL to the community by being one of the Community leads for both Philippine Data Platform Forums (formerly Philippine SQL Server User Group) and Singapore SQL PASS.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *