SQL Server Management Studio Connection to Integration Services Error Class Not Registered


By:   |   Updated: 2020-07-22   |   Comments (1)   |   Related: More > SQL Server Management Studio

Problem

Have you ever faced a problem with connecting to SQL Server Integration Services (SSIS) from SQL Server Management Studio (SSMS)? If so, you have probably checked whether the Integration Services service was running or not. Have you found that the service was running and SSMS was launched as an administrator, but you were still unable to connect? In this case, it is possible that you received the below error:

Connecting to the Integration Services service on the computer 'Computer Name' failed with the following error: "Class not registered".

If the above-mentioned situation is familiar to you and you still have not found the solution, this article is exactly for you.

Solution

Well, let's move forward to exploring the issue with an example, understand the cause and learn how to solve it.

In the described situation, we have SSIS service running and launched SSMS with administrator privileges from where we are trying to connect to Integration Services:

sql server object explorer

After clicking Integration Services, no such a service appears and, consequently, the "OK" button is not active:

connect to sql server integration services

If we click "Cancel" and type the local server name and then click "Connect", the following error appears:

ssis connection error

While the error message is not very clear, we can guess that the problem is related to the difference between the SQL Server and SSMS versions. Therefore, the first thing we do is to check the versions of both SSMS and SSIS.

To check the version of the local SQL Server (and, therefore, SSIS version), we run the following command in a SSMS query window (assumed we are connected to the local instance):

select @@version

The result shows the current version of SQL Server is 2017 (14.0.2027.2):

sql server version

To find the version of SSMS, we go to Help > About in SSMS:

ssms version

Thus, our current SSMS version is 18.4 (15.0.18206.0):

ssms version

According to Microsoft, however, in order to connect to the local Integration Services, the versions of the SSMS and SQL Server (on which SSIS is running) must be the same. Hence, we can conclude that the cause of the problem is the difference between the versions of SQL Server / SSIS (ver 14) and SSMS (ver 15). Installing an older version of SSMS corresponding to the version of the installed SQL Server will fix our problem. Let's follow this link to download the older version of SSMS.

In our case, we have to download version 17 of SSMS as highlighted below:

ssms release info

The description of the detailed installation of a standalone SSMS can be found in the "Downloading and installing standalone SSMS" section of the following link: How to Install SQL Server Management Studio on your Local Computer.

Now, let's install the downloaded standalone version of SSMS on our local computer:

install ssms

After having it installed, we can find out that we have one more version of SSMS on our computer. In our case, we have the 17th and 18th versions of SSMS on the same computer. Well, let's launch the older (17th) version of SSMS as an administrator:

run ssms as admin

When we are trying to connect to SSIS from this version of SSMS, unlike the previous example, here we can see that the local SSIS instance successfully appears:

connect to ssis

We can choose it and then click "OK" to connect. After doing that, we can see that the connection to the SSIS is successfully established:

objects in ssis via ssms

It is worth noting that we have to launch SSMS as an administrator to be able to connect to the SSIS. Otherwise, an error message will appear. We can illustrate this as well.

Let's run SSMS without choosing the "Run as administrator" option and then try to connect to SSIS. We will receive an error message, but this time the message is different mentioning "Access is denied".

connect to ssis from ssms as non admin error

Conclusion

In conclusion, using SSMS to connect to the local SSIS services running on a different version of SQL Server other than the version of SSMS, will cause an error. The issue can be fixed by installing the same version of SSMS as the corresponding SQL Server's version and connecting to SSIS using that version of SSMS. Additionally, SSMS must be launched with administrator's privileges to be able to connect to the SSIS service.

Next Steps

Please use the following links to find additional information about the discussed topic:



Last Updated: 2020-07-22


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources





Comments For This Article




Tuesday, August 04, 2020 - 8:25:43 AM - Mathew Osmanski Back To Top (86237)
Good article and a problem that has plagued me at many customers. May I suggest another option if available. Use SQL Server Integration Services Catalogs (SSISDB) to hold your packages and avoid connecting to Integration Services directly all together. The Catalog allows you to see all your SSIS Projects and Packages with a regular SSMS Database connection. There is also additional functionality such as enhanced logging, versioning, environment parameters, etc. that make using the SSISDB a much better resource. Note: Don't forget to change the database SSISDB to SIMPLE recovery model and edit the Integration Service Logs retention period to a reasonable time period if you enable the Integration Services Catalogs.

https://www.mssqltips.com/sqlservertip/4097/understanding-the-sql-server-integration-services-catalog-and-creating-the-ssisdb-catalog/


download


Recommended Reading

Display Line Numbers in a SQL Server Management Studio Query Window

Connect to SQL Servers in another domain using Windows Authentication

Executing large scripts in SQL Server Management Studio with Insufficient Memory Failures

SQL Server Database Diagram Index was Outside the Bounds of the Array

How to use RunAs command for SSMS if option does not exist





get free sql tips
agree to terms


Learn more about SQL Server tools