SSIS in a clustered environment

If you’re configuring SQL Server Integration Services (SSIS) in a clustered server environment, remember to change the value for the element in the SSIS configuration file to the virtual server name on each node of the cluster. If you don’t, SQL Server will probably throw this error:

SSIS MsDtsSrvr connection error
Login timeout expired. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. Named Pipes Provider: Could not open a connection to SQL Server (MsDtsSrvr)

Solution:
Run this query on the machine where you have SSIS installed:


select srvname from master.dbo.sysservers where srvid = 0

Navigate to the nodes of the machine where SSIS is installed and modify the configuration file:%Program Files%Microsoft SQL Server90DTSBinnMsDtsSrvr.ini.xml



You will have to replace the value of the element from a dot to the Virtual Server Name that was returned by the query because the dot resolves to the default SQL Server instance on the local server.

With SQL Server Named instances, change the value for the element in the MsDtsSrvr.ini.xml configuraton file.

Fire up SQL Server Configuration Manager, navigate to SQL Server 2005 Services and then restart SQL Server Integration Services.

Note: You may also have to configure remote access for Dcom Config for the MsDtsServer Properties (Security tab) in Component Services if running with a non-admin account.


Configuring the Integration Services Service : http://msdn.microsoft.com/en-us/library/ms137789.aspx

Configuring Integration Services in a Cluster: http://msdn.microsoft.com/en-us/library/ms345193(loband).aspx