The SQL server specified in SSIS service configuration is not present or is not available. This might occur where there is no default instance of SQL Server on the computer
Multiple versions of SQL Server installation is quite common when your enterprise is looking forward to upgrade to latest versions of SQL Server, in this regard recently one of the Application user reported a problem with regard to SSIS & DTS services on their server.
They are still on process of assessing the move from SQL Server 2000 to 2005 version, for the development purpose the test server has been setup with SQL Server 2000 as default instance and SQL Server 2005 as named instance. The main issues comes with SSIS tools, whenever trying to connect named instance getting the following error:
The SQL server specified in SSIS service configuration is not present or is not available. This might occur where there is no default instance of SQL Server on the computer
By default when you connect to Integration Services, you must specify the name of the server to connect to. If you cannot connect to Integration Services on the specified server, consider trying the following troubleshooting steps:
- Verify the startup type of the service. Open SQL Server Configuration Manager or the Services snap-in to verify that the service startup type is set to either Manual or Automatic. If the startup type is set to Disabled, you must change it to Manual or Automatic and start the service before you can connect to Integration Services.
- You do not have to start the service manually. When you connect to Integration Services in SQL Server Management Studio, the Integration Services service is started automatically, if it is not already running.
Also if you have firewall installed between the server & client instances with application-level filtering, you can use the user interface that Windows provides to specify the exceptions that are allowed through the firewall, such as programs and services. Otherwise, you have to configure DCOM to use a limited set of TCP ports. The Microsoft Web site link previously provided includes information about how to specify the TCP ports to use. If you connect to a named instance of SQL Server, you must update the configuration file to specify the named instance. If you do not update the configuration file, you cannot use Object Explorer in SQL Server Management Studio to view packages that are stored in the msdb database on the named instance. Also further notes to follow from Dinesh-Asanka blog entry.