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

Error:

Connecting to the Integration Services service on the computer “” failed with the following error: “The specified service does not exist as an installed service.”

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

Check & Findings:

1) SSIS services is running

2) I am using Newer SSMS version in compare to SSIS version.

Solution:

You will get this error message when you try to connect SSIS services from later version SSMS.

In my case, I am trying to connect SQL Server 2016 SSIS from SSMS of version 17. I have download version 16 SSMS and installed it. SSIS connectivity is working fine from version 16 SSMS.

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

After clicking the above link search for ” SSMS 16.5.3 ” and click ” English (United States) ” based on your language preference. It will start download, once download completes install and connect the SQL 2016 Intgartion Services successfully using SSMS 16.5.3

For More Info please read below :

When you install the Integration Services component of SQL Server, the Integration Services service is also installed. By default, the Integration Services service is started and the startup type of the service is set to automatic. However, you must also install SQL Server Management Studio to use the service to manage stored and running Integration Services packages.

Note

To connect directly to an instance of the legacy Integration Services Service, you have to use the version of SQL Server Management Studio (SSMS) aligned with the version of SQL Server on which the Integration Services Service is running. For example, to connect to the legacy Integration Services Service running on an instance of SQL Server 2016, you have to use the version of SSMS released for SQL Server 2016. Download SQL Server Management Studio (SSMS).

In the SSMS Connect to Server dialog box, you cannot enter the name of a server on which an earlier version of the Integration Services service is running. However, to manage packages that are stored on a remote server, you do not have to connect to the instance of the Integration Services service on that remote server. Instead, edit the configuration file for the Integration Services service so that SQL Server Management Studio displays the packages that are stored on the remote server.

You can only install a single instance of the Integration Services service on a computer. The service is not specific to a particular instance of the Database Engine. You connect to the service by using the name of the computer on which it is running.

You can manage the Integration Services service by using one of the following Microsoft Management Console (MMC) snap-ins: SQL Server Configuration Manager or Services. Before you can manage packages in SQL Server Management Studio, you must make sure that the service is started.

By default, the Integration Services service is configured to manage packages in the msdb database of the instance of the Database Engine that is installed at the same time as Integration Services. If an instance of the Database Engine is not installed at the same time, the Integration Services service is configured to manage packages in the msdb database of the local, default instance of the Database Engine. To manage packages that are stored in a named or remote instance of the Database Engine, or in multiple instances of the Database Engine, you have to modify the configuration file for the service.

By default, the Integration Services service is configured to stop running packages when the service is stopped. However, the Integration Services service does not wait for packages to stop and some packages may continue running after the Integration Services service is stopped.

If the Integration Services service is stopped, you can continue to run packages using the SQL Server Import and Export Wizard, the SSIS Designer, the Execute Package Utility, and the dtexec command prompt utility (dtexec.exe). However, you cannot monitor the running packages.

By default, the Integration Services service runs in the context of the NETWORK SERVICE account.

The Integration Services service writes to the Windows event log. You can view service events in SQL Server Management Studio. You can also view service events by using the Windows Event Viewer.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.