Configuring the Integration Services Service (SSIS Service)

Article
03/06/2017

Important

This topic discusses the Integration Services service, a Windows service for managing Integration Services packages. SQL Server 2014 supports the service for backward compatibility with earlier releases of Integration Services. Starting in SQL Server 2012, you can manage objects such as packages on the Integration Services server.

The Integration Services service relies on a configuration file for its settings. By default, the name for this configuration file is MsDtsSrvr.ini.xml, and the file is located in the folder, %ProgramFiles%\Microsoft SQL Server\120\DTS\Binn.

Typically, you do not have to make any changes to this configuration file, nor do you have to change the file's default location. However, you will have to modify the configuration file if your packages are stored in a named instance or a remote instance of Database Engine, or in multiple instances of the Database Engine. Also, if you move the configuration file to a location other than the default location, you will have to modify the Registry key that specifies the file location.

Configuration File Contents

When you install Integration Services, the setup process creates and installs the configuration file for the Integration Services service. This 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\120\DTS\Packages.

This configuration file also specifies which msdb database contains the packages that the Integration Services service will manage. By default, the Integration Services service is configured to manage packages in the msdb database of the instance of the Database Engine that is installed at the same time as Integration Services. If an instance of the Database Engine is not installed at the same time, the Integration Services service is configured to manage packages in the msdb database of the local, default instance of the Database Engine.

Default Configuration File Example

The following example shows a default configuration file that specifies the following settings:

Packages stop running when the Integration Services service stops.
The root folders for package storage in Integration Services are MSDB and File System.
The service manages packages that are stored in the msdb database of the local, default instance of SQL Server.
The service manages packages that are stored in the file system in the Packages folder.

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>

Modification of the Configuration File

You can 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. For example, you can create additional root folders of type, SqlServerFolder, to manage packages in the msdb databases of additional instances of Database Engine.

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.

However, you will have to modify the configuration file to manage packages that are stored in a named instance or a remote instance of Database Engine. 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 or the remote instance. If you try to use Object Explorer to view these packages, you receive the following error message:

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 2008 Books Online.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, 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 SQL Server [2]. (MsDtsSvr).

To modify the configuration file for the Integration Services service, you use a text editor.

Important

After you modify the service configuration file, you must restart the service to use the updated service configuration.

Modified Configuration File Example

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>

Modification of the Configuration File Location

The Registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\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\120\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. Note that the version number in the path (120 for SQL Server SQL Server 2014) will vary depending on the SQL Server version.

Caution

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.

Share via