Migrating SSISDB 2014 to 2019 RRS feed

  • 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?


    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.
       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


All replies


    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

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

    Thursday, July 9, 2020 1:06 AM