Getting the SSIS package store to work with a SQL Server named instance
By: Tim Cullen | Updated: 2009-04-09 | Comments (2) | Related: More > Integration Services Configuration Options
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?
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:
- 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.
- 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
Last Updated: 2009-04-09
About the author
View all my tips