Configuring the Integration Services Server

The Integration Services Server has a number of settings you can configure to customize its behavior. Although the default configuration values work for most users, occasionally you might want to change the settings to make it easier to manage your packages, be consistent with other applications and where they store their data, or just to have naming conventions that make it easier to remember which server is managing which packages. For example, you might want to store all packages on a central file share, making it easier to back up and refresh the packages along with any other important files in your system. The following settings can be used to customize the Integration Services Server in this way.

The configuration settings usually reside in a file called MsDtsSrvr.ini.xml found in the %Program Files%\Microsoft SQL Server\90\DTS\Binn folder. The location and name of the file is stored in the HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile Registry value. You can change the location and filename by modifying that Registry value to point to a file in a different directory, a file with a different name, or both. By pointing all your machines to the same server configuration file on a common share, you can enforce the same custom package store structure across all the machines in your enterprise.

Restarting the Server

Throughout this discussion, it's important to remember that the server loads these settings when it starts. When you make configuration changes, you need to restart the server. You can do that in a number of ways. Figure 17.15 shows how to restart the Integration Services Server in the Services Control Panel application. Right-click on the SQL Server Integration Services item and select Restart.

Figure 17.15. Restarting the server from the Services Control Panel

You can also restart the server from the SQL Server Configuration Manager, as shown in Figure 17.16. You can launch the SQL Server Configuration Manager from the SQL Server 2005 menu tree.

Figure 17.16. Restarting the server from SQL Server Configuration Manager

Server Configurations

So, let's talk a little about how you can configure the server. As mentioned earlier, the default configuration settings work for most people, so you aren't required to make any of these changes. However, if you want to customize the behavior, the following settings make that possible.

The default configuration file looks similar to the following:

<?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> 

The Header

The header is XML information and should not be changed. In the preceding file, the header is the first two nodes:

<?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"> 

Stop Executing Packages on Shutdown

This Boolean setting tells the server to attempt to shut down executing packages when it stops. The server uses the same mechanism here as it does when you select the option to stop a package in Management Studio, the Cancel event. Therefore, packages might not always terminate immediately.

Folders

There are two types of folders, SqlServerFolders and FileSystemFolders. You can create new roots for both of these if you want by adding a new node. For example, to create two new file system folders, you could add the following two nodes within the TopLevelFolders node:

   <Folder xsi:type="FileSystemFolder">      <Name>File System2</Name>      <StorePath>..\Packages2</StorePath>    </Folder>    <Folder xsi:type="FileSystemFolder">      <Name>File System3</Name>      <StorePath>..\Packages3</StorePath>    </Folder> 

The resulting folder structure in the Management Studio Integration Services Object Explorer Stored Packages tree would look similar to the one shown in Figure 17.17.

Figure 17.17. The modified file system folder tree

SQL Server Access

The SqlServerFolder also controls which SQL Server instance the server references when enumerating packages. This is something that will impact you if you're running Integration Services on a machine with multiple instances of SQL Server. The Integration Services Server does not support instances, but is aware of SQL Server instances and can be configured to reference a particular SQL Server instance. For example, on a given machine you may have an instance of SQL Server 2000 as the default instance, and SQL Server 2005 as a secondary instance called SQL2K5. To modify the SSIS Server to find packages in the SQL2K5 instance, you would change the ServerName value to point to the named instance, as follows:

<Folder xsi:type="SqlServerFolder">   <Name>MSDB</Name>   <ServerName>.\SQL2K5</ServerName> </Folder> 

Shared MSDB Store

In many scenarios, it is worthwhile to have all your packages stored on one or more central servers. Again, this makes it easier to secure, back up, and maintain the packages. To do this, you would change the server configuration file on all Integration Services Server machines to point to the same SQL Server machine. In this way, you use the server to indirectly access packages stored on SQL Server instances on other machines. The advantage here is that your automated setup need not change if the central SQL Server ever changes. You need only change the one setting in the server configuration and all your automated processes will continue to access the packages, even though they are stored in a different location. For example, suppose you have a batch file that runs a package using DTExec. The batch file can have the following DTExec command:

DTEXEC /Server MyLocalISServer /DTS "\"/MSDB/MyPackage\"" 

Later, if you decide to change the location of the packages, you can change the location in the server configuration file and the batch command remains valid. Also, in this way, end users can run packages without detailed knowledge of where they are stored, while giving control to administrators for where the packages are stored.

Server Diagnostics

There is another server configuration file called msdtssrvr.exe.config in the same folder as the msdtssrvr.ini.xml file. You can use it to turn on and modify server tracing. By default, the file looks like this:

<system.diagnostics>  <switches>    <add name="TraceClientConnections" value="0" />    <add name="TraceManagementCalls" value="0" />    <add name="ServerTraceSwitch" value="0" />  </switches>  </system.diagnostics> 

This is the default setting, which has all the trace switches turned off. If you want to have a view into what the server is doing, you can modify it to send trace output to a file, as follows. The name of the log file can be different, of course.

[View full width]
<?xml version="1.0" encoding="utf-8" ?> <configuration>  <runtime>    <gcServer enabled="true"/>  </runtime>  <system.diagnostics>    <switches>      <add name="TraceClientConnections" value="4" />      <add name="TraceManagementCalls" value="4" />      <add name="ServerTraceSwitch" value="4" />    </switches>    <trace autoflush="true">    <listeners>      <add name="FileLog" type="System.Diagnostics.TextWriterTraceListener"  initializeData="c:\temp\log\Listener.log"/>    </listeners>    </trace>  </system.diagnostics> </configuration> 

You can set different values for the three trace categories, as follows:

• 0 Do not log

• 1 Log errors only

• 2 Log errors and warnings only

• 3 Log more detailed error information

• 4 Log verbose trace information

When you restart the server and execute a package, the server writes log records similar to the following:

Reading configuration file C:\Program Files\Mi...\Binn\MsDtsSrvr.ini.xml Config processed: 4 root folders RegisterComObject: Enter DllGetClassObject: 0x0, System.__ComObject CoRegisterClassObject: 0 DllGetClassObject: 0x0, System.__ComObject CoRegisterClassObject: 0 RegisterComObject: Leave Dropped DtsApplication object Created DtsApplication object Opening client's process to get Exited event. GetRunningPackages Opening client's process to get Exited event. Registering package SafeMessageBox (ecf155ac-f433-4930-a0a1-be9b065e004d) for DIGITALDARKROOM\Kirk, assigned ID 2e570b07-5718-4d7b-b5e1-bfb1a6c0ddbf Package SafeMessageBox (ecf155ac-f433-4930-a0a1-be9b065e004d) registered by DIGITALDARKROOM\Kirk, assigned ID 2e570b07-5718-4d7b-b5e1-bfb1a6c0ddbf GetRunningPackages StopPackage 2e570b07-5718-4d7b-b5e1-bfb1a6c0ddbf Unregister package request: 2e570b07-5718-4d7b-b5e1-bfb1a6c0ddbf Package unregistered: 2e570b07-5718-4d7b-b5e1-bfb1a6c0ddbf 

The "4 root folders" entry lets you know how many folders are in the package folders tree. GetRunningPackages is a trace that happens whenever you refresh the packages tree in Management Studio and any other time the service attempts to enumerate running packages. The other bolded entries have to do with packages that execute. In this case, the package named SafeMessageBox was executing. From this log, you can see when a package executes, even if the package execution logs in the event log are somehow erased giving you a history of when packages have been run for a given server. Although not terribly useful for the general day-to-day functions of Integration Services, the trace functions can help you track detailed server activity that doesn't get logged by packages.

Caution

Modifying this file is not supported by Microsoft. If you modify it incorrectly, it can cause the server to fail. Be very careful when doing this and always back up the file before modifying it. Also, the format of this file might change over time, from release to release. Modify this file at your own risk.