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
June 29, 2020 at 4:18 pm
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/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2020 at 4:36 pm
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.
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):
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2020 at 6:05 pm
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,
June 29, 2020 at 7:13 pm
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:
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.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 14, 2020 at 10:13 am
Hi sqlfriend, I'm migrating SSISDB from SQL Server 2012 to 2016, and I met the same error, have you resolved it?
June 25, 2021 at 9:48 pm
I had the same issue. Steps to migrate SSISDB from older system to SQL2019 are:
-- On source server
BACKUP SERVICE MASTER KEY TO FILE= 'D:\SQLServiceMasterKey.SMK'
ENCRYPTION BY PASSWORD = '$qlhubReallyStr00ngPwd!'
GO
-- On target server
RESTORE SERVICE MASTER KEY FROM FILE='D:\SQLServiceMasterKey.SMK'
DECRYPTION BY PASSWORD = '$qlhubReallyStr00ngPwd!'
GO
USE SSISDB;
EXEC sp_change_users_login 'update_one', '##MS_SSISServerCleanupJobUser##','##MS_SSISServerCleanupJobLogin##'
C:\Program Files\Microsoft SQL Server\150\DTS\Binn\ISDBUpgradeWizard.exe
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply