Configuring the Integration Services Service

  • Article

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service. By default, the file is located in the folder, %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

The default configuration file contains the following settings:

  • Packages are sent a stop command when the service stops.
  • The root folders to display for Integration Services in Object Explorer of SQL Server Management Studio are the MSDB and File System folders.
  • The packages in the file system that the Integration Services service manages are located in %ProgramFiles%\Microsoft SQL Server\90\DTS\Packages.

By default, the Integration Services service is configured to manage packages that are stored in the msdb database in a local, default instance of Database Engine. To manage packages that are stored in a named instance or a remote instance of the Database Engine, or in multiple instances of the Database Engine, you have to modify the configuration file. For example, you can create additional root folders of the type, SqlServerFolder, to manage packages in the msdb database of multiple instances of the Database Engine.

You can also modify the configuration file to allow packages to continue running if the service stops, to display additional root folders in Object Explorer, or to specify a different folder or additional folders in the file system to be managed by Integration Services service.

The registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies the location and name for the configuration file that Integration Services service uses. The default value of the registry key is C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml. You can update the value of the registry key to use a different name and location for the configuration file.

Warning

Incorrectly editing the registry can cause serious problems that may require you to reinstall your operating system. Microsoft cannot guarantee that problems resulting from editing the registry incorrectly can be resolved. Before editing the registry, back up any valuable data. For information about how to back up, restore, and edit the registry, see the Microsoft Knowledge Base article, Description of the Microsoft Windows registry.

The Integration Services service loads the configuration file when the service is started. Any changes to the registry entry require that the service be restarted.

The configuration file can be updated by using a text editor. After you modify the service configuration file, you must restart the service to use the updated service configuration.

Note

Some characters are not valid in folder names. Valid characters for folder names are determined by the .NET Framework class System.IO.Path and the GetInvalidFilenameChars field. The GetInvalidFilenameChars field provides a platform-specific array of characters that cannot be specified in path string arguments passed to members of the Path class. The set of invalid characters can vary by file system. Typically, invalid characters are the quotation mark ("), less than (<) character, and pipe (|) character.

If you connect to a named instance of SQL Server, you must update the configuration file to specify the named instance. If you do not update the configuration file, you cannot use Object Explorer in SQL Server Management Studio to view packages that are stored in the msdb database on the named instance. If you try to use Object Explorer to view these packages, the following error message appears:

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2005 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

Named Pipes Provider: Could not open a connection to ssNoVersion [2]. (MsDtsSvr).

Default Configuration File

The following example shows a default configuration file for Integration Services. It specifies that packages stop running when the Integration Services service stops, that the root folders in Integration Services are MSDB and File System, and that the packages in the Packages folder are monitored by the service.

This file is for the default instance of SQL Server.

Example of a Default Configuration File

<?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>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>  
</DtsServiceConfiguration>

Modified Configuration File for a Named Instance of SQL Server

The following example shows a modified configuration file for Integration Services. This file is for a named instance of SQL Server called InstanceName on a server named ServerName.

Example of a Modified Configuration File for a Named Instance of SQL Server

<?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>MSDB</Name>
      <ServerName>ServerName\InstanceName</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>  
</DtsServiceConfiguration>
<?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">

See Also

Concepts

Integration Services Service

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

15 September 2007
New content
  • Explained that, to manage the msdb database on remote or multiple instances, you must modify the default configuration file.
  • Provided error message that appears if you try to view packages stored in the msdb database on a named instance of SQL Server without updating the configuration file appropriately.

15 September 2007
New content
  • Explained that, to manage the msdb database on remote or multiple instances, you must modify the default configuration file.

12 December 2006
New content
  • Added information about an error message that appears when you do not specify the named instance of SQL Server in the configuration file.
Changed content:
  • Corrected the name of the GetInvalidFilenameChars field and explained that invalid characters in folder names vary by file system.

14 April 2006
New content:
  • Added information about using a registry key to specify an updated name and location for the configuration file that Integration Services service uses.