SSIS and MSDB Packages in a Clustered Environment

There seems to be a lot of confusion about SSIS administration in a clustered environment, and I think a lot of it comes down to SQL error messages that occur in a variety of situations.

Take the following, when browsing the MSDB stored package folder when connected to an SSIS instance:

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 Server 2008 Books Online.

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (MsDtsSrvr)

Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 10.0)

This error, in short, says, "I can't access the SQL Server (not SSIS) instance where the MSDB database is stored.  Fix that, and then we'll talk."

Most of the solutions you'll find when searching the web on this error suggest that the problem is that you're using a named instance.  If that's the case, the solution is simple:  change the <servername> line in the MsDtsSrvr.ini.xml file (@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn) from the default of

      <ServerName>.</ServerName>

to

      <ServerName>.\instancename</ServerName>

where instancename is the name of your named instance.

When you're running in a clustered environment, however, we've got an additional wrinkle, and this is, I think, where a lot of the confusion comes in.

Since SSIS isn't cluster-aware, you connect to the SSIS instance on the individual cluster node (not the SQL cluster name).  That node, however, isn't where the MSDB database resides.

To fix this, we need to edit the MsDtsSrvr.ini.xml file
(@ <?>:\Program Files\Microsoft SQL Server\100\DTS\Binn)
and change the server name line to reflect the name of the SQL cluster so that SSIS knows where to look for the MSDB database.  Note that the SQL cluster name is not the cluster node (server) name.

So the change would look something like this:

      <ServerName>.</ServerName>

to

      <ServerName>sqlclustername</ServerName>

where sqlclustername is the name of your SQL Server cluster.

If you have a named instance, it would look like this

      <ServerName>sqlclustername\instancename</ServerName>

Note that you'll need to restart the SSIS service (on each node) after you update the configuration file.