Home > SSIS > Integration Services Configuration and Integration Services service configuration file

Integration Services Configuration and Integration Services service configuration file


Integration Services Configuration

There are 5 types of integration service configuration available to manage package object property configuration.

  • XML Configuration File
  • Environment Variable Configuration
  • Parent Package Configuration
  • Registry Configuration
  • SQL Server Configuration

Each integration services configuration as the flexibility of storing property value on top of its advantages and disadvantages.

XML configuration file

It is more flexible for deploying a package in different environment by just copy the source package and the configuration XML file to destination. But, XML configuration file is requires to store in a file system and we must secure this file.

Indirect configuration method that is environment variable can be used to map the physical path of the XML file.

Environment variable configuration

Package object properties and its values can be stored in environment variables.

In this method, we have to create environment variable for each property value.

Parent package configuration

It passes value to the child package while call from an parent package (Execute package task)

Registry configuration

It keeps the package property value in the windows registry.

SQL Server Configuration

It keeps both package property and value in SQL server.

Indirect configuration is possible by using environment variable that keeps the SQL Server configuration database connection string.

Property inside the package

We can pass package property value using command line option of DTEXEC utility  if the package is configured with parent package configuration, registry configuration or environment variable configuration.

Integration Services service configuration file

As the name tells you that it is The Integration Services service configuration file holds the integration services settings instead property and value as we discussed in the previous topic.

SSIS configuration file is located in %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn with the name MsDtsSrvr.ini.xml.

What is in integration service configuration file (MsDtsSrvr.ini.xml)?

<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>

It indicates that the package should stop running or continue running when Integration Services service stops. (True or False)

<TopLevelFolders>
    <Folder xsi:type="SqlServerFolder">
      <Name>MSDB</Name>
      <ServerName>.</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>

The root folders for package storage in Integration Services are MSDB and File System.

We can change the msdb database server location if it is named instance or in remote server. change here

      <ServerName>.</ServerName>

The service manages packages that are stored in the file system in the Packages folder.

      <StorePath>..\Packages</StorePath>

Change is the configuration files requires service restart.

Read further

Note 1: making changes in the service configuration file is to be avoided if you don’t have enough knowledge of what it is doing and what should you do after changes.

Note 2: This article refers to SQL Server 2008 R2 (update on 10/8/2011)

Thanks for reading.

Advertisements
  1. October 8, 2011 at 12:11 pm

    Make sure you plan ahead for SQL11 because there are big changes to how this work with the introduction of Environments:) Configurations go away, so plan accordingly.

    • October 8, 2011 at 7:58 pm

      Thanks Dan!

      I wrote this article for those who are still using older version of SQL Server. Of course, I have implemented the new feature in Denali. It is very much simplified. Thanks again for triggered me to write about Denali :).

      -Ayyappan

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: