I upgraded a SQL Server 2014 Standard instance to SQL Server 2019 Standard (also required upgrading the OS from Windows Server 2012 R2 to Windows Server 2019), and then patched to CU4.
During the SQL Server upgrade process, everything showed green checks at the end (including Integration Services), so nothing to indicate that there was an issue with upgrading the Integration Services (or any other features or components for that matter).
After doing the necessary reboots, now the SQL Server Integration Services 12.0 service will not start.
The Event Viewer Application log shows the following for Source SQLISService120 (Event ID 260):
Microsoft SSIS Service failed to start. Error: The SQL Server Integration Services service requires Integration Services to be installed by one of these editions of %SQL_PRODUCT_SHORT_NAME%: Standard, Enterprise, Developer, or Evaluation. To install Integration Services, run SQL Server Setup and select Integration Services.
I have a legacy 3rd party ETL solution that utilizes CLR and Service Broker that are hard-code dependent on the 12.0 Integration Service, so I need to get this running again as those ETLs are failing (despite having the newer SSIS version installed). Let's assume that I cannot change the application code, so I need to get this service running again.
I have already tried to use the SQL S014 (with Service Pack 2) installation media to try to install only the Integration Services feature (as suggested by the Event Viewer), but Integration Services is already checked and grayed out, preventing me from attempting to re-install it.
This 3rd party ETL solution throws the following error when they execute and fail:
Integration Services evaluation period has expired.
However, I'm not sure if that is a misleading error because the 12.0 service is offline, or if it found the binaries but thinks it is expired?
I wasn't here when this was initially implemented 6 years ago in 2014, so I don't think I have a way to confirm if this environment started as Evaluation then was upgraded to Standard.
I have already tried changing the
CommonFiles DWORD from 1 to 3 at
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\150\ConfigurationState, described in both "SSIS Evaluation Period Has Expired" on Dev Instance? and Microsoft guidance at https://support.microsoft.com/en-us/help/971268/evaluation-period-has-expired-error-message-when-working-with-sql-serv described in
Case 3 Option 2 section and re-running the SQL 2019 Upgrade Edition to try to get it to Standard Edition.
Not sure if this is a clue, but following the question posed by the answer in this post https://stackoverflow.com/questions/527839/ssis-package-fails-on-execute-integration-services-evaluation-period-has-expire/528479#528479 :
Check what version of DTEXEC you are using as default (start-run: cmd, dtexec)
When I do dtexec from a cmd prompt, it says it's
Version 12.0.5659.1 for 64-bit which seems to indicate the default dtexec is the old one.
Is there a way to change the default dtexec to be the new SQL 2019 version and perhaps that will allow the 3rd party ETLs to work (without needing the IS 12.0 service)?
What other options or ways do I have to get the Integration Services 12.0 service to start successfully so that I can run these legacy ETLs?