Thursday, November 28, 2013

SSIS 2012 has no default instance of SQL Server

On a SQL 2012 that I was recently given access to develop some new SSIs packages, I received the following error message when trying to connect with SSMS to the SSIS server:

The SQL Server instance specified in SSIS 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 2012 Books Online.

The solution is to find the name instance of SQL server that can be used as default instance on the server, in my case there were 2 named instances.

Once the instance name is known, you have to edit the SSIS configuration file MsDtsSrvr.ini.xml, The file is located in the folder, %ProgramFiles%\Microsoft SQL Server\110\DTS\Binn.

Edit this file, find the line with <ServerName>.</ServerName> and replace the dot by the 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>SERVER_NAME\INSTANCE_NAME</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..\Packages</StorePath>
    </Folder>
  </TopLevelFolders>
</DtsServiceConfiguration>