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

By: Tim Cullen | Comments (2) | Related: More > 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:

Error when attempting to view SSIS packages on a named instance

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

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>
Save the file
Restart the SQL Server and SQL Server Integration Services services
Re-connect to the SSIS instance and you should be able to see all packages stored in the SQL Server instance.

SSIS packages stored in the MSDB database

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.

View all my tips

Comments For This Article

Tuesday, April 21, 2009 - 9:39:43 PM - RobertLDavis	Back To Top (3228)
It doesn't have to be clustered to dao that. You can specify multiple instances whether clustered or not. See here for more info: http://www.sqlservercentral.com/blogs/robert_davis/archive/2008/12/11/How-Do-I-Configure-SSIS-to-Work-With-a-Named-Instance.aspx

Friday, April 10, 2009 - 6:04:04 AM - Firmbyte

If you have a clustered environment, you can configure your MsDtsSrvr.ini.xml to allow administration of packages on instances from each node.

The MsDtsSrvr.ini file example below shows an example of how to configure SSIS on each node of a cluster, so that the packages on each of the 3 instances can be managed from each node, the ini file is the same on both nodes.

/**** START ****/

<?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>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>SQL1 MSDB</Name>
      <ServerName>SQL1\SQLINST1</ServerName>
    </Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>SQL2 MSDB</Name>
      <ServerName>SQL2\SQLINST2</ServerName>
    </Folder>
    <Folder xsi:type="SqlServerFolder">
      <Name>SQL3 MSDB</Name>
      <ServerName>SQL3\SQLINST3</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
</TopLevelFolders>
</DtsServiceConfiguration>

/**** END ****/