Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS

If you are using SQL Server Management Studio in SQL 2012 to try to connect to an older SSIS instance (SQL 2008 R2 and earlier) called “SSISServerName”, you will get an error:

Connecting to the Integration Services service on the computer “SSISServerName” failed with the following error: “Class not registered”.

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

Not to worry, there is a workaround. It’s not the most logical workaround, but it will work. The SSIS service has a concept of “folders.” Read on for more details.

You see them when you connect to an SSIS instance through SSMS:
SSMS Folders for the SSIS Service

We can add additional folders that connect to remote instances (yes, even pre-SQL 2012 instances) by editing the MsDtsSrvr.ini.xml file on a SQL 2012 instance (perhaps your local machine) and restarting the SSIS service on the same machine. Here’s how.

  • Open the MsDtsSrvr.ini.xml file. It is typically located in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml.
  • Add a folder to the TopLevelFolders node. This folder should be configured to point to your remote non-SQL 2012 SSIS instance.
  • Restart the SSIS service.

An example XML file that adds a new folder that points to the named SQL instance, MyServer\SQL01 will look like:

<?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>.\SQL2012</ServerName>
      </Folder>
      <Folder xsi:type="FileSystemFolder">
         <Name>File System</Name>
         <StorePath>..\Packages</StorePath>
      </Folder>
      <Folder xsi:type="SqlServerFolder">
         <Name>MyServerFolder</Name>
         <ServerName>MyServer\SQL01</ServerName>
      </Folder>
   </TopLevelFolders>
</DtsServiceConfiguration>

Once the folder entry for “MyServerFolder” has been added, save the file, and then restart the SSIS Service via the Services Control Panel or via the SQL Server Configuration Manager.

Restart via SQL Server Configuration Manager

Then connect to your local SSIS service in SSMS to see the new folder. It should look as so:

Additional folder added.

Let me know if you have any additional questions.

7 thoughts on “Connecting to a pre-SQL 2012 SSIS Instance with SQL 2012 SSMS

  • parole-gtasanandreas.Blogspot.com

    Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your site?
    My blog site is in the very same area of interest as yours and my visitors would really benefit
    from a lot of the information you present here. Please let me know if this ok with you.
    Many thanks!

  • Peter

    Please Read this Microsoft article before using this method as it outlines the possible issues with this.

    http://support.microsoft.com/kb/2466166

  • Richard Granucci

    Like this. and tedious in that I now need to configure each server as a seperate folder entry, but such a modification is very seldom needed.

    However, I found, and this may be some sort of restriction on that SSIS server, but, I can connect to the older SSIS
    on an older SSMS, but not when I entered the correct info on the newer SSMS 2012. Like said I’ll have to continue to
    research why that is.

  • Michael Bergelson

    I tried to follow the instructions provided, but was not succesful in conntecting in any of the following scenarios:

    SSMS 2014 ==> 2012 IS server or a 2008 R2 IS server.
    SSMS 2012 ==> 2008 R2 IS server.

    The info provided is somewhat lacking. It would have been useful for the author to better instruct as to editing the XML file. For example, for both tags, & — where do those names come from (our imagination), meaning, to what to they hook in the SSIS server system? Or doesn’t it matter? It seems that “My Server Folder” is added to the IS server system.

    But in the illustration showing the results, there is nothing to connect with the server name in the XML code — the server name as entered in the XML code does not appear in the illustration. Instead it is shows as “localhost” with version info following.

    MyServerFolder
    MyServer\SQL01

    Why not show the simpler example of re-using the MSDB as the name? And then add that you can also create your own new folders and illustrate that? Too many variables thrown into the mix here without explanation.

    Which sadly, is sooooo typical of these tech-support sites, best intentions notwithstanding. Always only part of the story is provided. In these cases, “more is more!” Readers should not have to read the authors’ minds!

    IMHO

    • Phil Brammer

      Michael,
      I’m sorry you feel there are gaps.

      I’ll go through this with you.

      1) If you want to add a folder to your local SSIS instance called “MyServerFolder” follow the example XML file, with the bold section the new piece to add. The screenshot at the bottom shows how “MyServerFolder” is represented.
      2) The server name is the tag. In my example, it is showing how to connect to a named SQL instance, “MyServer\SQL01.” That is the connection to the SSIS server you want to manage from a 2012/2014 instance.
      3) If you can even re-use MSDB as the name, you wouldn’t be able to tell which server the “MSDB” folder is connected to.
      4) To make this work, you need to connect to the SSIS instance that you edited the XML file on. Typically, this is done on your local instance, which is why the last screenshot shows “localhost.” From there, the SSIS service connects to “MyServer\SQL01” and represents its SSIS packages under the “MyServerFolder” node.
      5) You can call “MyServerFolder” anything you want — it’s just a visual representation when connecting to your local SSIS instance (e.g. localhost in this case)

  • Darek

    Hi there. I’ve checked what M$ say about this workaround and there are issues with this approach. If you follow the advice of this article, you shouldn’t import/export packages and you shouldn’t run them by right-clicking on them and selecting the Run command. Otherwise there’ll be problems.

  • Phil Barr

    Couple of tips:
    ssms 2014 >> sqlsserver 2014 integration services
    …error: “class not registered” try deleting C:\Program Files\Microsoft SQL Server\120\DTS\Binn\MsDtsSrvr.ini.xml It worked for me. I’d save a copy first

    …error: “Access is denied” – run ssms as administrator.

    ssms 2014 >> earlier sqlsserver version integration services
    … not supported by Microsoft. If using a workaround to gain access older packages will be converted “on-the-fly” when executed, which may lead to unexpected results. It is best to install ssms 2012 sie-by-side and run older version dts packages from ssms 2012.

Comments are closed.