Get SSIS Package Store to Work with SQL Server Named Instance

Problem

We have a named SQL instance and I am able to connect to the instance, but when I try to view the SSIS packages stored in the MSDB database I get an error. This does not happen with our default instances. Is there an additional setting that must be changed to get this to work?

Solution

When a SQL Server instance is installed, one of the configuration files is the MsDtsSrvr.ini.xml file. This file is located in the “<Program Files Installation>\Microsoft SQL Server\90\DTS\Binn” folder. It is responsible for various configuration options. Here is what the default contents for MsDtsSrvr.ini.xml looks 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&;gttrue</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> 

Of the configuration settings listed above, the one of interest is the <ServerName> tag. By default, the configuration is set to look at default instance (the server name).

When you attempt to view the SQL Server Integration Services packages on a named instance, you get the following error:

In order to view packages on a named instance, perform the following steps:

1. Replace the period (.) in the <ServerName> section with the name of your SQL Server instance.  In my case this was changed to: <ServerName>CULLENSVR01\SQL2K5</ServerName>
2. Save the file
3. Restart the SQL Server and SQL Server Integration Services services
4. Re-connect to the SSIS instance and you should be able to see all packages stored in the SQL Server instance.

Next Steps

• Review information on Configuring the Integration Services instance for SQL Server 2005 and SQL Server 2008
• If you have clustering in your environment, read about alternatives to clustering the SSIS service for SQL Server 2005 and SQL Server 2008
• Visit the SQL Server Integration Services site for SQL Server 2005 and SQL Server 2008
• Read more SQL Server Integration Services tips on the MSSQLTips site

Tim Cullen

Tim has been working in the IT industry since 2003. After spending a few years as an “IT Generalist”, his career focus turned to SQL Server and application development. He currently works as an IT Specialist for a Federal agency in Maryland, where he focuses on SQL Server administration and providing guidance to teams on SQL Server performance issues. When not working, he enjoys playing golf, playing bass guitar, and exercising.

• MSSQLTips Awards: Trendsetter (25+ tips)