Getting the SSIS package store to work with a SQL Server named instance
Written By: Tim Cullen -- 4/9/2009
-- read/post comments
-- print --
Rating:
(not rated yet)
Rate
Problem
We have a named SQL instance and I am able to connect to the instance, but when I try to view the SSIS packages stored in the MSDB database I get an error. This does not happen with our default instances. Is there an additional setting that must be changed to get this to work?
Solution
When a SQL Server instance is installed, one of the configuration files is the MsDtsSrvr.ini.xml file. This file is located in the "<Program Files Installation>\Microsoft SQL Server\90\DTS\Binn" folder. It is responsible for various configuration options. Here is what the default contents for MsDtsSrvr.ini.xml looks like:
<?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&;gttrue</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> |
Of the configuration settings listed above, the one of interest is the <ServerName> tag. By default, the configuration is set to look at default instance (the server name).
When you attempt to view the SQL Server Integration Services packages on a named instance, you get the following error:
In order to view packages on a named instance, perform the following steps:
1. Replace the period (.) in the <ServerName> section with the name of your SQL Server instance. In my case this was changed to: <ServerName>CULLENSVR01\SQL2K5</ServerName>
2. Save the file
3. Restart the SQL Server and SQL Server Integration Services services
4. Re-connect to the SSIS instance and you should be able to see all packages stored in the SQL Server instance.
Next Steps
Readers Who Read This Tip Also Read
Free Live Webcast
Comment or Ask Questions About This Tip
|
