SSIS multiple Instances Package Store in SSMS

Recently I installed both SQL 2008 R2 and SQL 2012 on my new laptop. I installed SQL 2008 R2 as default and 2012 as named instance. Post installation while verifying all services in both the instances I realized that SSIS service doesnot support multi instance. When I tried to connect SSIS instance from 2012 management studio I got following error -

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. (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.

As per above error here the question is Why I can connect only to default instance of SSIS but not 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 and a named instance called MSSQLSERVER2012.

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>MSDB</Name>

             <ServerName>.</ServerName>

    </Folder>

       <Folder xsi:type="FileSystemFolder">

             <Name>File System</Name>

             <StorePath>..\Packages</StorePath>

       </Folder>

       <Folder xsi:type="SqlServerFolder">

             <Name>2012MSDB</Name>

             <ServerName>.\MSSQLSERVER2012</ServerName>

       </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.