Migrating SSISDB 2014 to 2019

Question

I restored our SSISDB from 2014 to 2019. However, we could not deploy packages getting the error below:

It looks like a schema issue and the proposed workaround worked when I tested this. Problem is, if you have a lot of packages, redeployment is something you would not want to do. Anyone have any tips or a different take on this?

TIA

TITLE: SQL Server Integration Services
------------------------------

The required components for the 64-bit edition of Integration Services cannot be found.  Run SQL Server Setup to install the required components.
A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": 
System.Data.SqlClient.SqlException: The required components for the 64-bit edition of Integration Services cannot be found.  Run SQL Server Setup to install the required components.
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext)
   at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.RaiseError(SysMessageId messageId, SysMessageSeverity severity, Object[] args)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.GetDtsPath(Int16 use32bitOn64, String& path)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.GetISServerExecPath(Int16 use32bitOn64, String& path)
   at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.DeployProjectInternal(SqlInt64 deployId, SqlInt64 versionId, SqlInt64 projectId, SqlString projectName)
. (Microsoft SQL Server, Error: 27222)

Answer 1

Hi ARPRINCE,

You can check the following post: Cannot upgrade SSIS catalog to 2019 after migration from 2017 server

Answer 2

The link pointed me to the right direction, thank you!

Anyway, I didn't know that there was an upgrade wizard (duh!).

C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe

However, it looks like this wizard will not work if you already patched SQL 2019 so you would get the error below:

The version of the sql server instance (15.0.4033.1) doesn't match with the version of the upgrade tool(15.0.2000). (Microsoft.SqlServer.IntegrationServices.ISServerDBUpgrade)

Lucky for me, in trying to resolve this issue since yesterday, I decided to install SQL 2019 into my laptop this afternoon. No CUs, no patch. So when I run the wizard, it converted my SSISDB.

Then, I restored the SSISDB into the new production server and we can now deploy the packages!!

Answer 3

Hi ARPRINCE,

Thanks for sharing the good news on your end. Glad to hear.