SSIS on multi-instance SQL Server 2005

Brief Description: SSIS on multi-instance SQL server does not recognize instances. Cannot attach at all, on a server with no default instance.


Symptom Description:

Attempting to attach to Integration Services on a multi-instance machine with SSIS installed gives the error

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Invalid Server name "Servername\instance". SSIS does not support multi-instance, use just servename instead of "servername\instance".

Problem:
SSIS on SQL 2005 servers running multiple instances is not intuitive to configure. One 'instance' of SSIS controls all SQL Server instances and cannot be addressed by an instance name. This is particularly frustrating if you have a SQL server with no default instance as it results in a connections being flat out refused.


Solution:
It can, however, be configured to recognize multiple MSDB databases on the same machine which effectively provides the same functionality.

To add a new instance, add a new Folder Record to the MsDtsSrvr.ini.xml

Backup the config file %SQL Install Root%\90\DTS\Bin\MsDtsSrvr.ini.xml

The default file should look something like (click for a more clear view)

To add an instance, add a new Folder Record

xsi:Type = "SqlServerFolder"
name = "MSDB Myinstancename"
Servername = "server\instance"

Edited File should look something like (click for a more clear view)










You may need to restart the SSIS service to attach