Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How Do I Configure SSIS to Work With a Named Instance

How Do I Configure SSIS to Work With a Named Instance? 

By default, SSIS (SQL Server Integration Services) uses the msdb database of the default instance on the server for storing packages. SSIS can still manage packages stored as files in the file system without any changes to the current setup. To be able to use the msdb database of a named instance, you have to manually edit the SSIS configuration file. Here are the steps for that:

  1. Shut down the IS service
  2. Find the configuration file: <install location>\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml
  3. Open the configuration file in a text editor such as Notepad
  4. To configure a named instance instead of the default instance, change the existing ServerName entry to ServerName\InstanceName under the Folder node of type SQLServerFolder
  5. To configure a named instance in addition to the default instance, add a new Folder node of type SQLServerFolder under the TopLevelFolders node
  6. Save and close the configuration file
  7. (Re)start the IS service

Before change:

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

After change to a named instance:

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


After adding a named instance to the default:

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

Comments

Posted by Ajay Prakash on 17 February 2009

I think this can be very useful where server capacity can be shared by multiple applications. i never thought towards this aspect.

While applying SQL Server patches for IS, will changing default cause some issue?

Posted by Robert Davis on 18 February 2009

To date, I have heard no reports of issues with changing the default server.

Posted by Anonymous on 9 February 2010

Pingback from  dtutil, named instance and package import &laquo;  Database Bulletin

Leave a Comment

Please register or log in to leave a comment.