You migrated an SSIS Catalog from another server by doing a backup and restore of the SSIDB. Restore completed successfully, but when you try to add folders in the SSIS Catalog, you receive the error below:
Exception has been thrown by the target of an invocation. (mscorlib)
Additional Information:
An exception occurred while executing a Transact-SQL statement or batch:
(Microsoft.SqlServer.ConnectionInfo)
Invalid object name ‘SSISDB.catalog.customized_logging_levels’ (Microsoft SQL Server, Error: 208)
Then, when you try to import a package, you receive the following error:
TITLE: SQL Server Integration Services
------------------------------
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
Cause:
You migrated an SSIS Catalog from a version lower than SQL Server 2016 to a SQL Server 2016 or later version.
SSIS Catalog has additional objects from 2016 onwards for custom logging as well as Availability Group-related objects. Even though when you restored the database, you saw the database was upgraded, this is only on the database engine compatibility level and not specifically for SSIS.
Solution:
To correct this, you’ll need to run the SSIS Database Upgrade task.
1. Navigate to the SSISCatalog using SQL Server Management Studio.
2. Right-click the SSISDB and choose Database Upgrade.
3. This will launch the SSIS DB Upgrade Wizard. Fill in the Instance name where your SSIS Catalog is and Click Upgrade, and it will upgrade the SSISDB to the version of the SQL Instance.
Sometimes running this from the SSMS yields an error where it cannot find the executable. When this happens, you can just simply navigate to the file location of the SSIS DB Upgrade Wizard executable. For example, below, it is in C:\Program Files\ Microsoft SQL Server\160\DTS\Binn. 160 is the SQL server version. In this example, it’s SQL Server 2022. Check your installation for the path and make sure you are running for the correct version. The executable filename is ISDBUpgradeWizard.exe.
This does not necessarily require a restart, but since you are changing binaries and registering dlls in the background, restarting the server is recommended. After this, you should be able to create folders and deploy packages without any issues.
For a step-by-step guide on restoring SSISDB from another server, you can refer to this post. You may also receive an error related to the master restoring SSISDB. For a guide on how to address that, refer to this post.
