Answered by:
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)
Wednesday, July 8, 2020 10:00 PM
Answers
-
Hi ARPRINCE,
You can check the following post: Cannot upgrade SSIS catalog to 2019 after migration from 2017 server
- Marked as answer by ARPRINCE Thursday, July 9, 2020 12:41 AM
Wednesday, July 8, 2020 10:17 PM
All replies
-
Hi ARPRINCE,
You can check the following post: Cannot upgrade SSIS catalog to 2019 after migration from 2017 server
- Marked as answer by ARPRINCE Thursday, July 9, 2020 12:41 AM
Wednesday, July 8, 2020 10:17 PM -
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!!
- Edited by ARPRINCE Thursday, July 9, 2020 1:14 AM
Thursday, July 9, 2020 12:56 AM -
Hi ARPRINCE,
Thanks for sharing the good news on your end. Glad to hear.
Thursday, July 9, 2020 1:06 AM