none
Can't connect to SSIS

    Question

  • Hi,

     

    I'm evaluating SQL server 2005 for planning a upgrade of our datawarehouse from SQL 2000 to 2005.

    We use a lot of DTS pacakages so that's my primary focus right now.

    I get this strange error when trying to connect to SSIS -> Stored Pacakges -> MSDB:

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

    ------------------------------
    ADDITIONAL INFORMATION:

    Login timeout expired
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    ------------------------------

    Login timeout expired
    An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Native Client)

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

    The install is on a x64 2003 with a x64 enterprise edition, with two instances. So what msdb is it trying to connect to since I can't seem to see this anywhere in management studio?

    Initially installed the two instances without named pipe support (only tcpip), but the naitive client was configured for named pipe support. I have now enabled named pipe support for both instances as well and afterwards restarted the server, but the problem persists.

    I've also checked the two instaces to verify that they allow remote connections, as well as doubling the query timeout period to 1200.

    I managed to move via DTS backup (non ms tool) a package from a test server onto the 2005 instance under Management -> Legacy -> DTS. Then I ran a migration of the imported pacakge and that ran smoothly as well.

    So now I would just like to see the result of the converted pacakage.

    Any clues to what I do wrong?

    Best regards

    SUN

     

     

    Thursday, February 16, 2006 10:50 PM

Answers

  • There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB.  By default, it is set as '.' which means the local server name, default instance.

    In your case, you probably have either a named instance, or are trying to connect to a non-default instance.  Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

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


    So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

    Friday, February 17, 2006 3:43 PM

All replies

  • There is a file called 'MsDtsSrvr.ini.xml' in the 'Program Files\Microsoft SQL Server\90\DTS\Binn' folder on each machine with SSIS installed that tells the SSIS service what SQL Server instance to connect to for enumerating packages stored in the MSDB.  By default, it is set as '.' which means the local server name, default instance.

    In your case, you probably have either a named instance, or are trying to connect to a non-default instance.  Basically, open that file, and change the ServerName element under the MSDB element to the name of the instance to connect to (in a clustered server case, use the virtual SQL instance name)...so, the file looks something like the following probably now:

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


    So, the part that I highlighted is what needs to be modified...just overwrite the '.' with the appropriate instance name and see if that works for you.

    Friday, February 17, 2006 3:43 PM
  • Hi,

    Thanks for the reply, Yes I installed both instances as named ones. So this seems to be the right solution. I'll test this out and see if it works on Monday. Thanks.

     

    //SUN

    Friday, February 17, 2006 4:38 PM
  • Tried the resolve and all worked well.  Just make sure you type the instance with the server name ie:

    {servername}\{instance name}

    Typing just the instance name failed.

    Thursday, March 23, 2006 3:54 PM
  • More importantly after you make the change to the XML file. You have to make sure you stop and restart the IIS service(Control Panal->Administration Tools->Services). Only after doing this it starts working.

     

    Friday, March 31, 2006 8:59 AM
  • Thanks for the info -

     

    Why isn't this a config property through the management console or config manager?

    Wednesday, April 19, 2006 6:28 AM
  • Having the same issue. 

    Tried to add server name, and server/instance to the file as suggested.  Restarted IIS each time as suggest.

    Still having problem connecting.

    I can "see" and import into BIDS packages created by the Import/Export Wizard. 

    If SSIS doesn't support instances as suggested when trying to connect to SSIS, how to handle this.  I have two instances of the DB,  Development and Test.  From there we would promote to production.  But on the dev box, how would you suggest we have development and test versions of our SSIS packages?

    Wednesday, June 14, 2006 6:55 PM
  • Thanks for the information. 

     

    Wednesday, June 21, 2006 10:53 AM
  • Restarting IIS did not do a thing. Restarting SSIS worked.

    Monday, June 23, 2008 8:07 PM
  • One additional note to this...

     

    After making this change -- when connecting in Management Studio to Integration Services -- do NOT type the instance name, type the default server's name (or local or .)

     

     

     

    Wednesday, August 06, 2008 7:40 PM
  • Hi,
    I recently installed SQL2005. I am facing the similar problem as I cant connect to my own server on my vista
    (
    Error 18456: Login failed for user 'DOMAIN\user"). Please suggest as to do what in this regards.
     
    <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>

    In you previous solution you said to change it '.' with the domain name. I can do that but my questiuon is how can I open the files (most of them are .dll extentions) in the destination =
    Program Files\Microsoft SQL Server\90\DTS\Binn. If only I can open the file then I will be able to do something.
    Thursday, November 13, 2008 6:09 AM
  •  

    Simply taking the default "." out and putting in the server\instance was easy enough.  Then a restart of SSIS.  This worked like a champ.  Confusing if you have multiple instances that you want in the MSDB folder though.  But I do not have that scenario, just one named.
    Monday, December 01, 2008 9:03 PM