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