SSIS Connection on Cluster Fails

SSIS Connection ErrorI ran into this problem recently when trying to export and import SSIS packages on a SQL Server 2008 R2 Cluster and it seemed like such a common problem, but I didn’t really find much documentation about how to resolve this.

SSIS Connection on Cluster Fails

This problem seems to always happen on SSIS (I only observed on SSIS 2008 R2) instances installed onto a server cluster. You can connect to the SSIS instance, but when you try to access Packages stored in the database it will try to connect for about a minute then return an error popup saying:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

The SQL Server instance specified in SSIS service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 R2 Books Online.

How to Resolve

Luckily, this is relatively easy to resolve, but it does require a restart of the SSIS Service.

  1. SSIS-1Find the SSIS installation location. By default it would be C:\Program Files\Microsoft SQL Server\100\DTS\Binn\
  2. Open MsDtsSrvr.ini.xml and locate the <ServerName> node. By default the value will be “.”
  3. SSIS-2Change the <ServerName> value to match your cluster virtual name. Save and close the file.
  4. SSIS-4Now restart the SSIS Windows Service to pick up the change.
  5. You should now be able to expand the MSDB folder within SSIS and view packages.

My New Stories

March 2016 Web Hosting Deals
Powershell AD Group Management
Troubleshooting 403