Problem

When trying to browse the SSIS packages from Management Studio the following error can apear:

The SQL Server specified in Integration Services service configuration is not present or is not available. This might occur when there is no default instance of SQL Server on the computer. For more information, see the topic "Configuring the Integration Services Service" in SQL Server 2008 Books Online.


Solution

This error appears when you have only named SQL instances on the machine, or when the default instance runs on a different port than the default one (1433).

To fix this edit the MsDtsSrvr.ini.xml file:

SQL 2005 location:
C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2008 location:
C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml

SQL 2012 location:
C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml

 

...

 

Modify the  MsDtsSrvr.ini.xml file to point to the your named instance or to the default instance with the right port, and then restart the SSIS service:

 

<?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> SERVER_NAME\INSTANCE_NAME</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

OR:

 

<?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> SERVER_NAME,PORT_NUMBER</ServerName>

    </Folder>

    <Folder xsi:type="FileSystemFolder">

      <Name>File System</Name>

      <StorePath>..\Packages</StorePath>

    </Folder>

  </TopLevelFolders>

</DtsServiceConfiguration>

 

 


3 comments

Comment from: Bonny [Visitor] Email
BonnyGreat. Thanks. It worked for me... :)
05/01/15 @ 01:19
Comment from: Mohammad [Visitor]
MohammadThank You, It worked for me .
11/08/15 @ 11:31
Comment from: lupji [Visitor]
lupjiI have cluster environment, and it is not working for me.

I put in the Servername instead of the individual cluster names.

12/08/15 @ 22:40

Leave a comment


Your email address will not be revealed on this site.
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)