SSIS service does not support multi-instance

I have a server with several SQL Server instances installed on it. Why can’t I connect to all instances of SSIS on my server? I can connect to the default instance, but not to the named instances.

The error I get is:

TITLE: Connect to Server
------------------------------
Cannot connect to MyServerName\test1.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Invalid server name "MyServerName\test1". SSIS service does not support multi-instance, use just server name instead of "server name\instance".
------------------------------
Invalid server name "MyServerName\test1". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

First things first: it is very common for users to think that the SSIS service is the one executing the SSIS packages. This is not true.

When we install a SQL Server instance on a server and we choose to add the SSIS components, a service is added to the machine which is called SQL Server Integration Services (MsDtsServer100). (You may see the services if you run services.msc command from the Run… menu).

So, what does this service do? It does NOT execute packages (the SSIS packages are executed by the DTExec utility). The SSIS service is used to provide connectivity to the SSMS for the user to be able to administer the SSIS packages contained in the package stores.

So, there is only one SSIS service per machine, which handles all requests for all instances.

Next question is Why can I connect to the default instance of the SSIS, but not to the named instance?

The reason is that the SQL Server SSIS installation does not automatically configure itself for all instances on the machine. And there is no UI to configure it either. What I mean is, that if we want to use the package stores for each instance on our machine, then we will need to do some configuration work.

Here is how:

  • navigate to the installation directory of the SQL Server, go to the DTS\Binn directory – in my case the path is C:\Program Files\Microsoft SQL Server\100\DTS\Binn, and within that folder there is a MsDtsSrvr.ini.xml file.
  • copy this file to a different location, for example your desktop (you will most likely not have permissions to edit the file in place and save it, so you will have to overwrite it after saving it to a different location)
  • open the file with any text editor

The file contents looks like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

And what we are interested is the and tags. What we would like to have is one entry for each instance on our machine. In my case I have 2 instances: default instance called MyServerName and a named instance called MyServerName\test1.

I am planning to use the SSIS package stores in both instances, so my config file should look like this:

<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
  <TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MyServerName MSDB</Name>
      <ServerName>MyServerName</ServerName>
    </Folder>
<Folder xsi:type="SqlServerFolder">
      <Name>test1 MSDB</Name>
      <ServerName>MyServerName\test1</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>

Now we have to overwrite the file in the Binn folder with the one we just saved. We have to restart the SSIS service, of course.
After this, we can connect from any server to both package stores of the instances.

Furthermore, this is valid for clustered instances of SQL Server. The SSIS service is not cluster aware, and there is barely any point for it to be cluster aware. What the above mentioned technique does, though is, it gives access to the SSIS package stores of each clustered instance.

Just make sure you list all needed SQL Server instances, and replicate the XML config file to all installation directories for the instances, so you can connect to the SSIS package stores from any server and from any instance.

Post comments if you have questions or send me a message through the Contact form.

 

 

Comments are closed.