Why can’t I install multiple instances of SQL Server Integration Services components on the same physical server?

The SQL Server Integration Services (SSIS) component of SQL Server 2005 runs as a service, and only one instance of SSIS can run on a single physical server at a time. If you are working with one instance of SQL Sever on a single physical box, the SSIS service will by default use the default instance to store packages (if you chose to store them within SQL sever). SSIS is shared by all instances of SQL Server running on the same physical server, but no matter which instance you use to create a SSIS package, the package will, by default, be stored by the default instance of SQL Server.

If you don’t have a default instance, or if out want to use a different SQL Server instance to store your packages on the same physical server, you must alter the SSIS configuration file to change the ServerName attribute.

This file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, with the file name of MsDtsSrvr.ini.xml, and it looks like what you see below.


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

While SSIS does not support multiple instances on a single physical server, it is aware of all SQL Server instances and can be configured to reference any particular instance by modifying the above configuration file.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |