SSIS With Non-Default Instance

If you’re like me and installed SQL Server 2000 as a default instance long before 2005 was even a mote in god’s eye, you may well be running SQL Server 2005 as a non-default (named) instance.

This works fine, until you want to run integration services against the named instance, and find out that it’s "not instance aware" (Microsoft’s words).  The result is that it barfs when you try to browse the list of packages on the server, with an error similar to:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)  

The answer to this is here.  In short, find the file C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml, and change the <Server> value from

<ServerName>.</ServerName>

to

<ServerName>machine\instance</ServerName>

- replacing machine with your machine name, and instance with your instance name.

Finally, restart the SQL Server Integration Services service.

Whoosh.