Write for Us
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.
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset
Try Idera’s new SQL admin toolset