Freigeben über

SSIS error “Failed to retrieve data for this request”: reasons and resolutions

  • Artikel

 

1.  After connecting to SQL Server Integration Services 2005 from SQL Server Management Studio, when you expand MSDB under Stored Packages to see list of stored packages, you may get below error.

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

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

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

ADDITIONAL INFORMATION:

The SQL server 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 Server 2005 Books Online.

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.

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (MsDtsSrvr)

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

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.

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. (Microsoft SQL Native Client)

Cause and Resolution

SSIS uses the configuration file located on the machine at "%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml". The content of file MsDtsSrvr.ini.xml typically looks like below.

 

<?xml version="1.0" encoding="utf-8"?>

<DtsServiceConfiguration xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns:xsi="https://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>

 If the ServerName specified between ServerName tags above is incorrect, you get the similar error mentioned above. To correct the issue, specify the correct name of the SQL Server with the instance name.

SSIS is not recommended to configure as a clustered resource, rather it is advised to install it separately on each node. Note, after installing in each node, by default SSIS will put “.” In the ServerName tag in the config file because SSIS is instance independent and always assumes to be installed on the default instance (which is “.”). So, in a clustered environment we need to specify “ClusterVirtualName\Instance name” where MSDB Database is hosted.

We need to restart the SSIS Windows Service for the changes to take effect.

 

2    When you try to connect to SQL Server Integration Services 2005 from SQL Server Management Studio from a client computer and the you use a user account that is not a member of the Administrators group on the sever, you may get below error.

 

Cannot connect to <computer-name>.

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

ADDITIONAL INFORMATION:

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

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

Connect to SSIS Service on machine <computer-name> failed:

Access is denied.

If you use the same user account to connect to SQL Server Integration Services 2005 from SQL Server Management Studio locally on the server, you will be able to successfully connect.

 

Cause and Resolution

This is a permission related issue. Please follow workaround provided below KB article to resolve the error.

940232 - Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: "Connect to SSIS Service on machine '<SSISServer>' failed Access is denied"

 

3. When you try to connect to SQL Server Integration Services 2005 from SQL Server Management Studio, you may get below errors.

 

Error 1

Cannot connect to <computer-name>.

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

ADDITIONAL INFORMATION:

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

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

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

Connect to SSIS Service on machine <computer-name> failed:

Error loading type library/DLL.

 

Error 2

Cannot connect to <computer-name>.

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

ADDITIONAL INFORMATION:

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

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

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

Retrieving the COM class factory for component with CLSID {E44847F1-FD8C-4251-B5DA-B04BB22E236E} failed due to the following error: 80070005. (Microsoft.SqlServer.ManagedDTS)

 

Error 3

Cannot connect to <computer-name>.

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

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Library not registered. (Exception from HRESULT: 0x8002801D
(TYPE_E_LIBNOTREGISTERED)) (Microsoft.SqlServer.DTSRuntimeWrap)

 

Causes and Resolutions

1. Unregister and register DTS.dll. If the SSIS is a 64-bit installation, unregister and register both 32-bit and 64-bit DTS.dll and MsDtsSrvrUtil.dll files. Commands are below.

Regsvr32 /u “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
Regsvr32 /u “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
Regsvr32 /u “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvrUtil.dll"
Regsvr32 /u “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvrUtil.dll"
Regsvr32 “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
Regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTS.dll"
Regsvr32 “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvrUtil.dll"
Regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvrUtil.dll"

This holds good for Error #1(Error loading type library/DLL.) and Error #3(TYPE_E_LIBNOTREGISTERE))

This error is encountered frequently when a Service pack is applied over an existing installation of Sql Server. During SP installation Sql automatically unregisters these dlls and on successful completion re-registers them. We have seen issues where the re-registration fails and Customer gets the above errors while connecting to SSIS after installation of a Service Pack.

2. Unregister and register SqlTaskConnections.dll. If the SSIS is a 64-bit installation, unregister and register both 32-bit and 64-bit SqlTaskConnections.dll files. Commands are below.
Regsvr32 /u “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ SqlTaskConnections.dll"
Regsvr32 /u “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ SqlTaskConnections.dll"
Regsvr32 “C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\ SqlTaskConnections.dll"
Regsvr32 “C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ SqlTaskConnections.dll"

3. Make sure that the SSIS startup account has full permissions on folder
C:\Program Files\Microsoft SQL Server\90\DTS\Binn where the SSIS binaries reside.

4. Re-install Sql Server 2005 Client tools and Workstation Components.

 

Author : Deepak (MSFT) , SQL Developer Engineer , Microsoft

Reviewed by : Debarchan (MSFT) , SQL Developer Engineer , Microsoft

Comments