Issues connecting to SSIS and browsing packages in msdb when database service is clustered


In Sql Server 2005, you cannot cluster Sql Server Integration Service (SSIS).

During database cluster install, you can pick to install Integration Service. SSIS will only be installed on the node where installation process starts from. For example, suppose you have Node1 and Node2, and the install is started from Node1, then database service and binaries will be installed on both nodes automatically, but SSIS will only be installed on Node1.

Now, suppose the database cluster name is VirtualDb. When it is running on Node1, to connect to Integration Service in Sql Server Management Studio, you can choose to connect to VirtualDb, because under the cover, you are really connecting to Node1, which does have SSIS installed. However, if VirtualDb happens to run on Node2, then you have a problem, because there is no SSIS on Node2. Instead, you will have the message below:

Connect to SSIS Service on machine “VirtualDb” failed:
Class not registered
——————————
BUTTONS:

OK
——————————

So, SSIS wise, you can only connect to Node1 or VirtualDb when it is running on Node1, but we are not done yet. You will get an error when you try to browse Stored Packages -> MSDB in Object Explorer. Below is the error you will get:

The SQL server 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 2005 Books Online.

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 [2]. (MsDtsSrvr)

——————————
BUTTONS:

OK
——————————

The reason is that the meta data of package location is controlled by a file located on Node1, and part of it is incorrect. If you browse through the path of Sql Server, look for C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Open it up and you will see this piece of code:

    <folder xsi:type="SqlServerFolder">
      <name>MSDB</name>
      <servername>.</servername>
    </folder>

Here, you can see that the ServerName is a dot, which means default instance of Sql Server on this local machine. To fix the problem you encountered earlier while browsing packages, you will need to change it to VirtualDb. After that, restart SSIS, and it will work for you in Management Studio. The dot here would have worked, had the database service been a non-clustered service. I do believe this should be treated as a Microsoft bug. Instead of just putting a dot here by the installer, it should put in the virtual database server name, when database service is installed as a cluster.

Hope this information will help somebody.


3 responses to “Issues connecting to SSIS and browsing packages in msdb when database service is clustered”

  1. If Node1 is down completely, the database will be failover to Node2 but SSIS is not, then applications requiring SSIS will be in trouble.

    Do you have any idea how we should configure a high-availability SSIS environment?

    Thanks.

  2. I found your posting very very helpful. It assisted me in a confusing situation.

  3. kt, you will need to look up information on Windows clustering. Yes, it can be done.

    Excellent, Belingue. Glad it helped.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.