none
Upgrade and migrate SSIS 2017 to 2019 SQL server --deploy errror RRS feed

  • Question

  • I am working on to migrate databases from a SQL server 2017 to 2019 new SQL server.

    Other databases work fine. But I have errors when running packages in the SSIS catalog.

    So I though I  need to upgrade the packages in MS Data tool first.

    I open VS 2019 data tool, and changed the project that has these packages to deploy to TargetServerVersion SQL server 2019.

    I  can see it  converts those packages, I guess  that is the process of upgrade packages.

    By the way after that, I also tried Right click the SSIS packages folder, and click upgrade all packages, but there is existing packages showing there, so I guess the packages have already been upgraded.

    Now I build the project and try to deploy it to my new server.  But at last step of the deployment wizard, it says:

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

    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 

    .....

    What does the  bolded errror message mean? 

    How to fix this.

    Thanks in advance

     

    Thanks

    Monday, June 29, 2020 12:02 AM

All replies

  • Hi msloy,

    "...Version=14.0.0.0..." means SQL Server/SSIS version 2017.

    It seems that the SSIS is still looking for SSIS 2017.

    • You need to make sure that new SQL Server 2019 instance has SSIS component installed too.
    • Make sure that VS' switch to TargetServerVersion SQL server 2019 actually worked.
      Test your SSIS packages in VS first, before any deployment.

    Monday, June 29, 2020 12:23 AM
  • Thank you!

    Yes, I also noticed the version =14.0.0.0. which I don't understand why it looks for 2017 SSIS. On the new SQL server I installed 2019 SSIS. And restore the database SSIS from old server to the  new server, and I think it is upgraded too.  In VIsual studio, I can see it changed to targetServerVersion 2019, and I rebuild the project after that. Still like that.

    When I run the packages in Visual studio 2019, it works. but cannot deploy and has above error message

    Any ideas what else I can troubleshoot, thanks!


     



    Thanks


    • Edited by msloy Monday, June 29, 2020 12:34 AM
    Monday, June 29, 2020 12:33 AM
  • From the error it appears that the packages are still referencing the 2017 runtime binaries. I suspect the packages did not get set to the correct target version. 

    Look at the package xml code and find any line with the string "VSTAMajorVersion=". This is the scripting version for the package. 

    SSIS 2017 will have a value of "14"

    SSIS 2019 will have a value of "15"

    To change the package target version choose the project properties, Configuration Properties, General, then select the TargetServerVersion. If you are changing the target version you will get a warning about what you are about to do, click Yes. After this you may also get a message that files have been changed externally. Click Yes to All.


    If my reply solves your issue, please mark it as the answer

    Monday, June 29, 2020 2:30 AM
  • Thanks I DID change the TargetServerVersion to 2019 and it is saved there.

    I noticed from server itself if I run to check the version of the schema, it shows 14.0.0 too.

    So that means the db is moved but not upgraded to 2019 schema.

    I created another post so that it does not complex this one. 

    Please see here 

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/460958bf-f413-4233-b6c5-441ba9bb035f/cannot-upgrade-ssis-catalog-to-2019-after-migration-from-2017-server?forum=sqlintegrationservices


    Thanks

    • Proposed as answer by Lily Lii Monday, June 29, 2020 7:12 AM
    Monday, June 29, 2020 3:12 AM
  • Hi msloy,

    Thank you for your reply. It's so kind of you to mark helpful replies or your own reply as answers in order to close this thread. By doing so, it will benefit all community members who are having this similar issue.

    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:12 AM
  • I cannot find in the xml VSTAMajorVersion string. I can find is:

     DTS:ExecutableType="Microsoft.Package"
      DTS:LastModifiedProductVersion="15.0.2000.123"
      

    ...

      DTS:Name="PackageFormatVersion">8</DTS:Property>

    I am using the newest SQL server intergration services Project version 3.8 data tool.


    Thanks

    Monday, June 29, 2020 3:18 PM
  • I don't think this one is answered yet, so I cannot mark it as an answer. Thanks

    Thanks

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

    This issue indicates there is a new Assembly that has the external_access setting in the same database. Have you tried granted the Unsafe assembly permission?

    steps: Do Not Set Trustworthy On for the SSISDB 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


    • Edited by Lily Lii Tuesday, June 30, 2020 8:23 AM
    Tuesday, June 30, 2020 8:22 AM