(Attempting to) Upgrade the SSIS Catalog to SSIS 2016

A couple people have asked me about upgrading the SSIS Catalog from previous versions to SQL Server 2016 so I decided to blog about it.

The SSIS Catalog is an SSIS Execution Framework that ships with SQL Server versions starting with 2012. To execute SSIS packages using the Catalog, the SSIS project must be built using the Project Deployment Model and deployed the SSIS Catalog. You can learn more about the process at the MSDN topic: Deployment of Projects. All SSIS Catalog functionality is encapsulated within a database named SSISDB.

As this MSDN article explains, SQL Server 2016 ships with a utility named the SSISDB Upgrade Wizard:

The Adventure Begins

I decided to try it by restoring a backup of an SSISDB database to SQL Server 2016:

The restore worked as expected:

And the SSISDB database appeared in my list of databases in SSMS Object Explorer:

So far, so good. I clicked the Start Windows icon in Windows Server 2016 Technical Preview 5 and browsed All Applications until I found the SQL Server 2016 Upgrade Wizard link:

Once the upgrade wizard opened, I entered the name of the SQL Server 2016 instance that hosts the restored SSISDB database and then checked the “I have taken a backup of the SSISDB database. Continue with the SSISDB upgrade” checkbox. I clicked the Upgrade button to begin the upgrade process:

I received this error:

Sad Trombone…

The text of the error message reads:

CREATE ASSEMBLY for assembly ‘Microsoft.SqlServer.IntegrationServices.Server’ failed because assembly ‘Microsoft.SqlServer.IntegrationServices.Server’ is not authorized for PERMISSION_SET = UNSAFE. The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
Changed database context to ‘SSISDB’.
Warning: The SQL Server client assembly ‘microsoft.sqlserver.integrationservices.server, version=13.0.0.0, culture=neutral, publickeytoken=89845dcd8080cc91, processorarchitecture=msil.’ you are registering is not fully tested in SQL Server hosted environment. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

I keyed on “PEERMISSION_SET”. My first thought was security and I know from experience the SSISDB database employs Transparent Data Encryption (TDE). So I began searching for information on how to restore a database that uses TDE. I found a helpful post at http://dba.stackexchange.com/questions/3388/restore-encrypted-database-to-another-server and executed a query I found in an answer:

[sql]OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword' ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY GO[/sql] [sql]The query succeeded, so I retried the SSISDB Upgrade Wizard and got an new error:[/sql] [sql][/sql] [sql]The error message text is:[/sql]

Cannot drop the assembly ‘ISSERVER’, because it does not exist or you do not have permission.
Changed database context to ‘SSISDB’. (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

I tried again, thinking I should re-restore the SSISDB database, then run the query to fix the TDE. No dice, same error as shown above. As of the time of this writing, I haven’t figured it out. I will update this post when I do.

In the meantime, I decided to take

A Different Approach…

… and use Catalog Compare to Migrate to the SSIS 2016 Catalog.

:{>

Learn more:

Related Books

Help!
Enterprise Data & Analytics – We are here to help.

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

5 thoughts on “(Attempting to) Upgrade the SSIS Catalog to SSIS 2016”

I manage two servers and ran into your last issue “Cannot drop the assembly ‘ISSERVER’, because it does not exist or you do not have permission.” on one of my servers. For whatever reason the assembly ISSERVER does not exist when the upgrade wizard wants to drop and recreate it during the upgrade process.
I resolved the issue by running a Profiler trace, running the upgrade wizard, and finding the steps to manually create the new assembly. With the assembly in place the upgrade wizard can drop and recreate it without issue. Here is the TSQL to manually create the ISSERVER assembly.
DECLARE @keypath NVARCHAR(1024) = N’SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS\Setup\DTSPath’
DECLARE @path NVARCHAR(255)
DECLARE @fullPath NVARCHAR(255)
EXEC master..xp_regread ‘HKEY_LOCAL_MACHINE’, @keypath, N”, @path OUTPUT
SET @fullPath = REPLACE( @path, ””, ””” ) + N’Binn\Microsoft.SqlServer.IntegrationServices.Server.dll’
PRINT(@fullPath);
— Use OpenRowset to read the binary bits into @asm_bin
EXECUTE (‘DECLARE @asm_bin varbinary(max);
SELECT @asm_bin = BulkColumn FROM OPENROWSET (BULK N”’ + @fullPath + ”’,SINGLE_BLOB) AS dll
CREATE ASSEMBLY ISSERVER FROM @asm_bin WITH PERMISSION_SET = UNSAFE’);

Hi Andy,
Since you are moving the SSIS catalog from one instance to another while upgrading the catalog I recommend that you or anyone else finding this article taking a look at the requirements for moving the SSIS catalog. This might be your missing link. It has helped me out in the past.
https://msdn.microsoft.com/en-us/library/hh213291.aspx
Regards,
John

The ISSERVER assembly failed to be dropped might because of the following reasons:
1. The current active database is not SSISDB
2. You are not the owner of this database
3. The assembly doesn’t exist
4. The trustworthy setting of the SSISDB is OFF
5. This ISSERVER assembly is referenced by some other assembly
To resolve this issue,
Firstly, you should check whether this assembly exists or referenced by other assembly.
1. If this assembly doesn’t exist, create a fake one to unblock the entire upgrade.
2. If this assembly is referenced by some other assembly, remove the dependency before the upgrading.
Then, make sure the you have the permission to drop the folder by execting the following script:
1.Changing database owner of SSISDB to ‘sa’
use SSISDB
go
sp_changedbowner ‘sa’
2. Change trustworthy setting
ALTER DATABASE SSISDB SET TRUSTWORTHY ON

1. Create new SSIS Catalog
2. Drop database SSISDB only
This does not delete objects outside the SSISDB database (Logins, etc.)that belong to the SSISDB catalog. Don’t drop SSISDB Catalog!
3. Restore Database SSISDB
4. Restore Master Key
5. Upgrade SSISDB with UPGRADE Wizard
Background Information for Manual Creation (SQL Server 2014)
https://msdn.microsoft.com/en-us/library/hh213291(v=sql.120).aspx

What Cory did by reinstalling the assembly helped me with a patching issue with SQL Server 2016. Once the assembly was reinstalled I was able to patch SQL correctly. It seems if you have SSIS installed and remove it then try to patch SQL while the SSIDB and Catalog are still installed you run into problems

Andy Leonard

5 thoughts on “(Attempting to) Upgrade the SSIS Catalog to SSIS 2016”

Leave a Reply Cancel reply