SQL Server SSIS configuration issues

Recently I ran into a problem where I was unable to load a SSIS package into a SQL 2005 Server instance.  I received the following error:

 

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.

Login Timeout Expired

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

 

After a little bit of Googling I found the problem to be a configuration file that was not set up properly.  There is a file called MsDTSSrvr.ini.xml that resides in the folder C:Program FilesMicrosoft SQL ServerXXXDTSBinn, where XXX is the numeric equivalent of the SQL server version running.  In this file there is an XML markup for the server name.  In my case it looked like this:

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

Notice the Server name tag used the "." which denotes the default instance.  Unfortunately my SQL instance I was attempting to load my SSIS package into was not the default instance.

Changing the XML file to have my instance allowed me to add the SSIS properly.  Please note you also need to stop and restart the SQL services in order to have this change take place:

<?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>MYSQLSERVERMYINSTANCE</ServerName>
    </Folder>
    <Folder xsi:type="FileSystemFolder">
      <Name>File System</Name>
      <StorePath>..Packages</StorePath>
    </Folder>
  </TopLevelFolders> 
</DtsServiceConfiguration>

ABOUT THE AUTHOR

Kris Halsrud

Kris Halsrud is a Senior Analyst / Developer for Customer FX Corporation.

1 Comment

  1. Do not work me also.

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!