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


By:   |   Updated: 2009-04-09   |   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:

  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


Last Updated: 2009-04-09


get scripts

next tip button



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.

View all my tips





Comments For This Article




Tuesday, April 21, 2009 - 9:39:43 PM - RobertLDavis Back To Top

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

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



download


Recommended Reading

Setup Environment Variables in SQL Server Integration Services

SQL Server Integration Services SSIS Package Configuration

Understanding the SQL Server Integration Services Catalog and creating the SSISDB Catalog

Import, Export, Copy and Delete SSIS Packages

SSIS Project Deployment Model in SQL Server 2012 (Part 1 of 2)





get free sql tips
agree to terms


Learn more about SQL Server tools