By: Tim Cullen | 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:
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.
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
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips