Cannot connect to SQL Server Integration Services

Issue

I am unable to connect to SQL Server Integration Services (SSIS) running on SQL Server 2016 SP1 CU3 using SQL Server Management Studio 17.8.1.  This is the error message thrown when attempting to connect to the SSIS instance:

SQL1

SQL2

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 “SQLDEV” 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.

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

This is a new SQL Server 2016 installation on Windows Server 2016.

SSIS is installed correctly and the service is running.

Connections to the Database Engine succeed.

Resolution

There is a big clue in the error message, although at first I dismissed it as it’s referring to SSIS 2005: 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.”

The build number of SSIS is 13.0.5026.

The build number of SQL Server Management Studio 17.8.1 is 14.0.17277.0.

SSIS is very finicky when it comes to establishing connections from SQL Server Management Studio that don’t share the same major build version.

I removed SQL Server Management Studio 17.8.1, and installed the most recent version of SQL Server Management Studio (16.5.3), build number: 13.0.16106.4.

Sorted, I’m able to connect:

SQL3

SQL Server Management Studio, current and previous versions are available here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s