I’m back to writing! With so many talks in recent times and those who still come, and with it all the time necessary to prepare them; with so much work and new projects ongoing (thankfully); with 3 kids at home and recently married… it has been difficult to arrange a free time to concentrate on the writing. But I’m back, and for starting with a smooth topic that I like: “Errors and Warnings, Causes and Solutions” on a problem that actually I faced today while trying to connect with SQL Server Integration Services (SSIS): “The specified service does not exist as an installed service.”
Today, while I was trying to access SSIS from SQL Server 2016 Server, that host and support BizTalk Server 2016 I got the following and bizarre error:
TITLE: Connect to Server
——————————
Cannot connect to ..
——————————
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476
——————————
Connecting to the Integration Services service on the computer “localhost” 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.
Cause
This was bizarre because again I was trying to access to SSIS directly from SQL Server machine and I was sure that I had Integration Services installed and running on the server as I was able to confirm access to the services (services.msc).
I’m not a SQL Server specialist, but after careful research into the SSIS documentation it says:
“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.”
That triggered some red lights on my head because:
- I knew that this was a recent installation and we were using a current version of SQL Server Management Studio (v17.9.1);
- and I also knew that for example during the BizTalk Server configuration we may face some issues configuring some features if we are using a recent version of SSMS, you should use a compatible and recommended version: SSMS 16.5.3.
Solution
So, to solve this issue, you should:
- “Downgrade” your SSMS version to SSMS 16.5.3 that you can download here: https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms?view=sql-server-2017#download-ssms-1653
- Install SSMS 16.5.3 in another machine to access SSIS.
- Or accessing from BizTalk Server 2016 machine that should have this SSMS version installed.
In my case, I was able to connect to SSIS without any problem from SSMS installed in BizTalk Server 2018 machine because I always installed from day one SSMS 16.5.3 on BizTalk Servers machines.
So funny. I just ran into the same problem this week. In addition to the SSMS version, I also had permission issues. Not only did I need to be granted access to SSIS in “component services” on the SQL Server by the DBA team, they also had to add me in to the Distributed COM Users group. There was a lot of trial-and-error, internet searching, and folklore involved.
I am having the same problem for only one user and that user had all admin permissions to access the server. Still he is getting the same error even we installed lower version SSMS.
Others in the group can able to connect SSIS normally. I have been working on this issue for past two weeks. But no luck.
Really Appreciate your help.