Getting the SSIS package store to work with a SQL Server named instance

By: Tim Cullen   |   Updated: 2009-04-09   |   Comments (2)   |   Related: > Integration Services Configuration Options

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

About the author

Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips