SQL Server Integration Service (SSIS) 2016 unable to connect from SQL Server Management Studio (SSMS) 17.X.

Error:

The following problem occurred while connecting to the Integration Services service on the computer “”: “The given service does not exist as an installed service.”

When using the current version of the SQL Server tools to connect to a SQL Server 2005 Integration Services service, you may receive this error. Instead, add directories to the service configuration file to allow the SQL Server 2005 instance’s local Integration Services service to manage packages.

Check & Findings:

1) SSIS services is running

2) In comparison to SSIS, I’m utilising a newer SSMS version.

Solution:

When you try to connect SSIS services from a later version of SSMS, you’ll see this error notice.

In my scenario, I’m using SSMS version 17 to connect to SQL Server 2016 SSIS. I downloaded and installed version 16 of SSMS. From SSMS version 16, SSIS connectivity is operating fine.

Download links for various SSMS versions (16.X & 17.X)– https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-changelog-ssms

After you’ve clicked the above link, look for ” SSMS 16.5.3 ” and select ” English (United States) ” as your preferred language. It will begin downloading; once the download is complete, use SSMS 16.5.3 to instal and connect the SQL 2016 Integration Services.

Please read the following for more information:

The Integration Services service is installed when you install the SQL Server Integration Services component. The Integration Services service is launched by default, and its startup type is set to automatic. To utilize the service to manage stored and running Integration Services packages, you must additionally install SQL Server Management Studio.

.

Note

You must use the version of SQL Server Management Studio (SSMS) that corresponds to the version of SQL Server on which the legacy Integration Services Service is operating to connect directly to an instance of the legacy Integration Services Service. To connect to the historical Integration Services Service running on a SQL Server 2016 instance, for example, you must use the SSMS version issued for SQL Server 2016.. Download SQL Server Management Studio (SSMS).

You cannot enter the name of a server running an earlier version of the Integration Services service in the SSMS Connect to Server dialogue box. To handle packages stored on a remote server, however, you do not need to connect to that distant server’s instance of the Integration Services service. Instead, modify the Integration Services service’s configuration file so that SQL Server Management Studio shows the packages stored on the remote server.

On each machine, only one instance of the Integration Services service can be installed. The service is not tied to a single Database Engine instance. You use the service to connect to it.

The SQL Server Configuration Manager or Services snap-ins in the Microsoft Management Console (MMC) can be used to manage the Integration Services service. You must first start the service before you can handle packages in SQL Server Management Studio.

The Integration Services service is set up by default to handle packages in the msdb database of the Database Engine instance that was installed with Integration Services. The Integration Services service is set to manage packages in the msdb database of the local, default instance of the Database Engine if an instance of the Database Engine is not installed at the same time. You must edit the service’s configuration file to handle packages stored in a named or remote instance of the Database Engine, or in many instances of the Database Engine.

When the Integration Services service is halted by default, all running packages are terminated. The Integration Services service, on the other hand, does not wait for packages to terminate, and some packages may continue to run after the Integration Services service has been terminated.

You can still run packages using the SQL Server Import and Export Wizard, the SSIS Designer, the Execute Package Utility, and the dtexec command prompt utility if the Integration Services service is stopped (dtexec.exe). However, you are unable to keep track of the running packages.

The NETWORK SERVICE account is used by default to run the Integration Services service.

The Windows event log is written to by the Integration Services service. SQL Server Management Studio allows you to view service events. The Windows Event Viewer can also be used to view service events.