none
cannot upgrade SSIS catalog to 2019 after migration from 2017 server RRS feed

  • Question

  • I move a SSIS database and other user databases  from SQL server 2017 to a new server 2019.

    Other databases work fine. SSISDB does not work as expected.

    I restored the SSIS database and master key, successfully.

    But When I try to run database upgrade from SSMS by right click SSISDB -- Database upgrade I got an error:

    The system cannot find the file specified (System)

    Then I tried from the wizard directory on the file system

    D:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard, 

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

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

    So I still cannot upgrade the SSIS from 2017 to 2019.

    If I validate the package it will gives the error noting 2019 not available still 2017:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    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=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. An error relating to security occurred. (Exception from HRESULT: 0x8013150A)
    System.IO.FileLoadException:
    at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
    at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
    at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean forIntrospection)
    at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)
    at System.Reflection.Assembly.Load(String assemblyString)
    (Microsoft SQL Server, Error: 10314)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=15.00.4043&EvtSrc=MSSQLServer&EvtID=10314&LinkId=20476

    Please help how to fix this,

     

    Thanks much in advance


    Thanks

    Monday, June 29, 2020 3:09 AM

Answers


  • Then I tried from the wizard directory on the file system

    D:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard, 

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

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

    So I still cannot upgrade the SSIS from 2017 to 2019.


    I recall getting this error when I tried to upgrade after applying a SQL Server 2019 CU. The work-around that worked for me was to upgrade the catalog on SQL Server 2019 RTM first (using the wizard), and then apply the CU.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by msloy Tuesday, June 30, 2020 5:02 PM
    Tuesday, June 30, 2020 4:40 PM

All replies

  • This is partly a guess, I haven't seen the error you mention, but "moving"the SSIS catalog seems to be something full of hidden traps.

    I found these articles from Pinal Dave that I would give a try, they are note entirely about your issue, but they might point you in the right way. I feel like the major issue is that it asks for an assembly from SQL 2017
    Could not load file or assembly 'microsoft.sqlserver.integrationservices.server, Version=14.0.0.0

    So maybe one of these hold the clue. might it be that you are missing some components in your installation, or that there is a prerequisit that it just don't openly tell you about?
    https://blog.sqlauthority.com/2016/01/13/sql-server-system-io-filenotfoundexception-could-not-load-file-or-assembly/
    https://blog.sqlauthority.com/2018/05/11/sql-server-error-could-not-load-file-or-assembly-microsoft-sqlserver-management-sdk-sfc-version-12-0-0-0/

    As mentioned, this is a little shooting from the hip...

    Monday, June 29, 2020 5:48 AM
  • Can you just re -publish the packages to the new server?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 29, 2020 6:23 AM
  • Hi msloy,

    Restoring SSISDB to another server is similar to migrating all the SSIS projects you have deployed to Integration Services for the SQL Server Instance. 

    refer to these steps: How To Restore SSISDB To Another Server And Migrate The SSIS Catalog.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, June 29, 2020 7:36 AM
  • Thank you, yes, I agree what you said "moving"the SSIS catalog seems to be something full of hidden traps.

    I followed MS article for moving SSIS database, and upgrade database, no luck.  I don't think I missed any components when I do the initial install on the new server for SSIS. But after migrate from 2017 server, restore the database and restore the master key, I got this error. I tried to upgrade the database, and got the next error described in my top post.

    The two sqlauthority links seem not related with my cases. Thanks


    Thanks

    Monday, June 29, 2020 3:23 PM
  • to re-publish is my final approach to try.

    But I still want to know from DBA point of view how to move a SSIS catalog from 2017 to 2019 and how to upgrade it correctly without error. 

    If this is OK, then I don't need to involve multiple developers to re-deploy their packages to new server.


    Thanks

    Monday, June 29, 2020 3:25 PM
  • Hi msloy,

    1. What about Lily's link: How To Restore SSISDB To Another Server And Migrate The SSIS Catalog.
      Did you have a chance to try it?
    2. Handy SQL:

    USE SSISDB;
    
    EXEC catalog.check_schema_version @use32bitruntime = 0;




    Monday, June 29, 2020 3:29 PM
  • Hi, the link you gave me is one of the articles I already used as reference to do my work. I even commented in the below of the article about assemblies.

    I have searched on line quite a few articles before posting

    So please see my original post at the top. 

    Esp, at this step, what  does this mean: The version of the sql server instance (15.0.4043.16) doesn't match with the version of the upgrade tool(15.0.2000).

    Thanks


    Thanks


    • Edited by msloy Monday, June 29, 2020 3:32 PM
    Monday, June 29, 2020 3:32 PM
  • So when I ran the  upgrade database tool wizard, I got The version of the sql server instance (15.0.4043.16) doesn't match with the version of the upgrade tool(15.0.2000)

    The SQL instance is the on the CU 5 of 2019, the newest one, what about the upgrade tool, how come they are not matching?

    Is this again a microsoft bug?


    Thanks

    Monday, June 29, 2020 3:35 PM
  • Hi msloy,

    Are you following this link when you got the versions mismatch?

    Upgrade the SSIS Catalog (SSISDB)

    Another link: FIX: ISDBUpgradeWizard.exe throws error when you try to upgrade SSISDB after restoring from earlier versions in SQL Server 2019

    You can try to run the SSISDB upgrade wizard directly from C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe

    Monday, June 29, 2020 3:42 PM
  • Yes, I did follow that article too. We don't use always on. just stand alone sql instance

    Thanks

    Monday, June 29, 2020 3:45 PM
  • I also ran this before my post:

    USE SSISDB;
    
    EXEC catalog.check_schema_version @use32bitruntime = 0;

    I got 14.0.3045.24.

    This is after I restore the database from SQL 2017 to this 2019 server


    Thanks

    Monday, June 29, 2020 3:46 PM
  • Hi msloy,

    You can try to run the SSISDB upgrade wizard directly from C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe

    Monday, June 29, 2020 3:51 PM
  • Thanks, 

    that is what I did, see my top post.


    Thanks

    Monday, June 29, 2020 3:53 PM
  • Hi msloy,

    IMHO, you need to contact MS Support.

    This bug was supposed to be fixed in SQL Server 2019 CU3.

    Monday, June 29, 2020 3:56 PM
  • Thank you.

    I  don't have support case any more.

    How come the bug come back again?


    Thanks

    Monday, June 29, 2020 3:59 PM
  • Hi msloy,

    You still can open a ticket with MS PSS.

    If it is a bug, MS will not charge you.

    Monday, June 29, 2020 4:06 PM
  • Thank you!

    Thanks

    Monday, June 29, 2020 4:20 PM
  • Hi msloy,

    Thank you for your patient reply, if it is a bug, it's so kind of you to come back and share it with us. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 30, 2020 7:11 AM
  • I submitted to microsoft connect, but  I don't know when can be responsed

    Thanks

    Tuesday, June 30, 2020 3:47 PM
  • Hi msloy,

    Microsoft connect was shutdown some years ago.

    They have Azure feedback now as its replacement.

    And there is already an existing post there on the same subject:

    https://feedback.azure.com/forums/908035-sql-server/suggestions/34723897-ssisdb-upgrade-failure

    Tuesday, June 30, 2020 4:16 PM

  • Then I tried from the wizard directory on the file system

    D:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard, 

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

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

    So I still cannot upgrade the SSIS from 2017 to 2019.


    I recall getting this error when I tried to upgrade after applying a SQL Server 2019 CU. The work-around that worked for me was to upgrade the catalog on SQL Server 2019 RTM first (using the wizard), and then apply the CU.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Marked as answer by msloy Tuesday, June 30, 2020 5:02 PM
    Tuesday, June 30, 2020 4:40 PM
  • Thanks Dan.

    That is something I  thought at last of this research. But I already recreated the SSIS catalog and deploy from VS, not using the restore approach.

    I really still want to know the correct path of restoring and upgrade approach. Your answer may be the solution.

    For I have other servers in future to migrate, and this is something good to try.

    And also I think microsoft should fix this, because it does not make sense the upgrade wizard only works with RTM, but not newer CU.

    Thanks


    Thanks

    Tuesday, June 30, 2020 5:02 PM