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




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools