none
error when migrating SSISDB to new server of newer version of SQL RRS feed

  • Question

  • I tried to move SSIS database catalog on SQL server 2014 to a new server SQL 2017.

    I created the catalog first on new server, and then restored the database SSISDB from old server to new server.

    And restored the master key.

    Then I run the following stored procedure and got an error:

    Use SSISDB

    go

    EXECUTE   [catalog].[check_schema_version]
       @use32bitruntime=1

     Msg 10314, Level 16, State 11, Procedure catalog.check_schema_version, Line 117 [Batch Start Line 2]
    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=12.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)

    If I use  parameter @use32bitruntime=0, it is also the same error.

    It seems it tried to find 2014 version of  assembly. But my new server is newer version of 2017,

    Basically I follow the instruction on this page:

    https://blog.pythian.com/how-to-restore-ssisdb-to-another-server-and-migrate-the-ssis-catalog/

    Now what I should do to fix this error?

    Thanks much!


    Thanks



    • Edited by msloy Wednesday, October 16, 2019 5:43 PM
    Wednesday, October 16, 2019 5:33 PM

Answers

  • I do not know what is in that blog.

    You could just create the SSIS ctalog (SSISDB) on the new, 2017 instance and then deploy the projects to it thereby upgrading them to the new (2017) format.

    There is no way this can work as a restore to a new format in my view.

    Here is a link to help you https://docs.microsoft.com/en-us/azure/dms/how-to-migrate-ssis-packages

    It says Azure, but it must work equally well for a non-Azure based SQL Server instance.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by msloy Wednesday, October 16, 2019 8:28 PM
    Wednesday, October 16, 2019 6:27 PM
    Moderator
  • Hi msloy,

    SSISDB Catalog 2017 cannot house SSIS projects and packages outside of 2017 version. The conversion is happening in the background during deployment process.

    • Marked as answer by msloy Wednesday, October 16, 2019 8:28 PM
    Wednesday, October 16, 2019 7:56 PM

All replies

  • I do not know what is in that blog.

    You could just create the SSIS ctalog (SSISDB) on the new, 2017 instance and then deploy the projects to it thereby upgrading them to the new (2017) format.

    There is no way this can work as a restore to a new format in my view.

    Here is a link to help you https://docs.microsoft.com/en-us/azure/dms/how-to-migrate-ssis-packages

    It says Azure, but it must work equally well for a non-Azure based SQL Server instance.


    Arthur

    MyBlog


    Twitter

    • Marked as answer by msloy Wednesday, October 16, 2019 8:28 PM
    Wednesday, October 16, 2019 6:27 PM
    Moderator
  • Thanks.

    So do you mean if I deploy from the older server (SQL 2014) using SSMS catalog menu deploy wizard to the new server, it then will be automatically upgraded to the new 2017 format?

    Is there a way to check it is upgraded to 2017 version?

    Thanks



    Thanks

    Wednesday, October 16, 2019 7:10 PM
  • Hi msloy,

    SSISDB Catalog 2017 cannot house SSIS projects and packages outside of 2017 version. The conversion is happening in the background during deployment process.

    • Marked as answer by msloy Wednesday, October 16, 2019 8:28 PM
    Wednesday, October 16, 2019 7:56 PM