upgrade SSIS catalog got an error

  • sqlfriend

    SSC Guru

    Points: 52433

    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 restore 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 pacakge it will gives the error noting 2019 not availble 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

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    This may be a silly question but did you install the 2019 SSIS along with the 2019 SQL Instance?

    Next, does the following path exist:

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

    Those would be the first things I'd check.  I'd also check for any updates (CU's and SP's) as this may be a known issue.

    When you say you "moved" the database over, did you change he compat level to 150?  I expect that SSISDB needs to be in compat 150 for SSIS 2019.

    My last thought would be to re-deploy the SSIS packages to the server.  From Visual Studio you can change all the packages to 2019 mode and then push them up to the server in bulk.

     

    EDIT - another thought, might help to change the database owner to sa.  source (not directed at SSIS, but at the error you saw) - https://sqlwithmanoj.com/2016/06/22/sql-error-the-server-may-be-running-out-of-resources-or-the-assembly-may-not-be-trusted-with-permission_set-external_access-or-unsafe/

  • sqlfriend

    SSC Guru

    Points: 52433

    Thank you Brian.

    Yes, I do install SQL SSIS instance, from the configuration manger I can see SQL server integration services 15.0 is running.

    I changed the compatibility level to 150. And run the wizard from D:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard, still failed.

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

    My last thought too, is to try to delete current SSIS catalog and SSIS db that stored from 2017. And recreate the new SSIS catalog and deploy the package from VS.

    But as a DBA, I really want to see how this moving  SSIS and upgrade to work without  involving multiple developers.

     

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    Yeah, I'm a bit curious as well.

    The error about version mismatches makes me think that the SQL instance was patched, but that the ISDBUpgradeWizard was not.  Are there outstanding SQL patches that need to be applied?

    What is weird is the official fix from Microsoft is to install CU3 (which you already applied as you are on CU5):

    https://support.microsoft.com/en-us/help/4547890/fix-isdbupgradewizard-exe-throws-error-when-you-try-to-upgrade-ssisdb

    I am wondering if it is a bug in CU5.  As a thought, did you install the RTM version of 2019, restore databases then install CU1, CU2, CU3, CU4, and finally CU5 or go straight from RTM to CU5 or slipstream CU5 into RTM?  My guess is that Microsoft screwed something up so even though it SHOULD have included that fix in CU5, it somehow got missed.

    To fix this on your side, I expect it will be a process and will depend on how the install went.  If it was slipstreamed, I THINK you will need to uninstall SQL 2019 and reinstall the RTM and then bring the databases into it, then do the updates to the SSIS packages and finally install the CU's.  If you installed RTM then installed one or more of the CU's, you can likely remove the CU(s), upgrade the SSIS pacakges, and re-apply the CU(s).

    Not ideal by any means.  I suppose the other approach would be to reach out to Microsoft as this SHOULD be patched already and you shouldn't be  running into the issue.  By bringing it up to MS, you may get it patched in the next CU.

  • sqlfriend

    SSC Guru

    Points: 52433

    Thank you!

    Yes, that is exactly what I think too. Maybe my version (CU5) is too new for the upgrade wizard, or it has a bug in CU5.

    I installed RTM first, then right after it I applied CU5, then I restore database and ran upgrade...

    For now, I think I will just recreate the catalog and deploy from VS.

    But I  expect this could be fixed by microsoft in next CU.

    How can  I reach them, I don't have support ticket any more.

    Thanks,

  • Mr. Brian Gale

    SSC-Insane

    Points: 22932

    How to contact them for support depends on your support contract with your license (I believe).

    I would reach out to the license vendor first, and failing that this link has information on how to get support from Microsoft:

    https://support.microsoft.com/en-us/help/4047326/support-policy-for-microsoft-sql-server#:~:text=Microsoft%20provides%20technical%20support%20and%20product%20fixes%20for,hardware%20architectures%20in%20accordance%20with%20the%20product%20documentation.

    has a lot of reading, but as long as you are in a supported platform, then you can skim down to the bottom and find the "Obtain support from Microsoft" section.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply