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

By:   |   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:

Error when attempting to view SSIS packages on a named instance

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.
SSIS packages stored in the MSDB database
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen 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



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 Back To Top (3164)

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 ****/















get free sql tips
agree to terms