Friday, September 30, 2011

Managing multiple instances of SQL Server Integration services


When you have more than one instance of SQL Server Integration Services installed on a single box, you will have to tweak the MsDtsSrvr.ini xml file in order to properly work with the multiple instances.
I have two instances of integrations Servcices installed on my box - one is Integration Services 2005(default instance) and the other one is Integration Services 2008(named instance).
The first one, Integration Services 2005 works with out any issues as expected. But when I try to connect to Integration Services 2008 using SQL Server Management Studio, I will get this error:

Failed to retrieve data for this request.
SSIS service does not support multi-instance, use just server name instead of "server name\instance".
To get around this error, we need to configure the MsDtsSrvr.ini.xml file which is available under the path C:\Program Files\Microsoft SQL Server\100\DTS\Binn. Open the MsDtsSrvr.ini.xml file in notepad and edit it as shown in the figure below.

Please note that in the original file, the server name which is denoted by a '.' has been replaced byServerName\InstanceName, where ServerName is the name of the server on which Integration Services has been installed and InstanceName is the named instance of Integration Services.
After saving the xml file, be sure to start the Integration Services service for changes to take effect. Now when you connect to Integration Services(2005 or 2008) using SQL Server management studio, be sure to just use the server name. To connect to Integration Services 2008, use SSMS 2008 and to connect to Integration Services 2005, use SSMS 2005.

3 comments:

  1. Hey vегy іnteresting blog!

    kate dircksen

    ReplyDelete
  2. Actually you can connect to both instances of Integration Services without making any changes and using only the server name to connect.
    Use SSMS 2005 to connect to Integration Services 2005 using the Server Name (no instance name accepted) - when connected you can see the same version for both Integration Services and Database Engine.
    Use SSMS 2008 to connect to Integration Services 2008 using the Server Name (no instance name accepted) - when connected you can see the same version for both Integration Services and Database Engine.

    Gina Alexa

    ReplyDelete
  3. Tried so many way to export my SSIS packages from an old 2008 SQL instance and this is the only one that helped. Fixed everything and got them all exported, THANK YOU!!!

    ReplyDelete