I was recently trying to do some development work with SSIS. I had SQL 2012 installed on a development VM and had issues connecting to an SSIS 2008 R2 instance on a different server.

You can either install multiple versions of SSMS on your developer machine and use the appropriate version when connecting to different servers or you can perform the work around outlined below.

In essence you modify the MsDtsSrvr.ini.xml file on your machine to have a pointer to the other machine. You may have to modify the permissions on the file / folder so that you can save your changes.

Then in SQL Server Management Studio you connect to your local SSIS instance and the folder will be available showing the SSIS objects on the other machine.

 

The steps below were taken from the following article by Phil Brammer on the subject http://www.ssistalk.com/2012/03/21/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”.

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:

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.

  1. Open the MsDtsSrvr.ini.xml file. It is typically located in C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml.
  2. Add a folder to the TopLevelFolders node. This folder should be configured to point to your remote non-SQL 2012 SSIS instance.
  3. 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.

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

There are a few caveats to this work around as described in the following Microsoft Support article that you should be aware of.

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

Runtime: When you execute an SSIS 2005 package using the object explorer in  SSMS2008 (by right clicking on the package and selecting Run), the object explorer will launch the 2008 (or later) version of   DTExecUI.exe runtime on the client machine to execute the package. Since that involves executing a 2005 package using a 2008 version of the tool, the tool (DTEXecui.exe) will automatically upgrade the package format to the newer 2008 package format before is it executed. However, since the newer format is not persisted just by running the package, your package will still exist as a SSIS 2005 package that can still be accessed by SSMS from a SQL Server 2005 instance.

Import & Export: Making package changes by Importing or Exporting packages via this object explorer interface across versions is not recommended. SSIS 2008 package format could be inadvertently saved into the SQL 2005 MSDB or file system, or SSIS 2005 packages can inadvertently be upgraded to SSIS 2008 format upon export to MSDB or file system. When this happens, the package formats could become mismatched and the upgraded packages can no longer be run using SSIS 2005 runtime. When you use Import or Export functionality from Management Studio in SQL Server 2008 or later versions,  it persists the package in the format that matches the version of Management Studio, instead of the original 2005 format. Though the runtimes from SSIS 2008 or SSIS 2008 R2 can execute SSIS 2005 packages, SSIS 2005 runtimes cannot execute SSIS 2008 (or later version) packages.

You can look at the following XML tag inside a persisted .Dtsx package file to notice the difference in package formats:

SSIS 2005 Format:

<DTS:Property DTS:Name=”PackageFormatVersion”>2</DTS:Property>

SSIS 2008 Format:

<DTS:Property DTS:Name=”PackageFormatVersion”>3</DTS:Property>

 

Hopefully this will help some others in the future when they encounter this.

 

Any interesting work-arounds you have for SSIS?

Let us know in the comment below.