My Favorite Blogs

Monday, October 19, 2009

Connect to SSIS Service on machine "ServerName" failed: Error loading type library/DLL.

Last week I was requested to install SSIS on an existing SQL instance, I installed the SSIS successfully but it failed to open in the SQL Server Management Studio (SSMS). After some troubleshooting and msn tips, I understood that is a declared bug on MS SQL 2005.

The Existing SQL instance version is 9.0.3215 which is running on Windows 2003 64bit Edition. So I’ve to install SISS with SP2 and Hotfix 3215. I’ve installed successfully the SSIS component (hope you aware how to add a component on an existing SQL instance or refer http://support.microsoft.com/kb/913967 ) and installed SP2 and Hotfix 3215 on top of it. All went fine and rebooted the server as per the hotfix requirement.

After the installation I tried to open the SSIS on SSMS, but it failed to open and thrown the below error.

Error Description

TITLE: Connect to Server
------------------------------
Cannot connect to ServerName.
------------------------------
ADDITIONAL INFORMATION:
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
------------------------------
Connect to SSIS Service on machine "ServerName" failed:
Error loading type library/DLL.

------------------------------
Connect to SSIS Service on machine "ServerName" failed:
Error loading type library/DLL.

------------------------------
BUTTONS:
OK
------------------------------

Solution

As per Microsoft Knowledge Base, when you install SSIS on an existing SQL server 2005 instance on 64-bit server, the installer unregisters the 32-bit DTS.DLL file while installing the 64-bit DTS.DLL file. The 64-bit DTS.DLL file may be installed with SP2 and also may be with hotfix.

To resolve this problem, you have to register two dll files manually 1) dts.dll and and 2) MsDtsSrvrUtil.dll. For 32-bit version of SSIS you’ve to register only 32-bit dlls but for 64-bit version of SSIS , you have to register both 32-bit and 64-bit dlls. Because as per my experience, first I’ve installed only 64-bit version of DLLs but I got the below error then I registered 32-bit version also, then it worked fine. Refer http://support.microsoft.com/kb/919224 for more details.



Registering DLLs manually

Go to command prompt (Start --Run -- Cmd)

cd c:\windows\system32

Registering 64-bit version dlls
regsvr32 "C:\Program Files\Microsoft SQL Server"\90\DTS\Binn\dts.dll
regsvr32 "C:\Program Files\Microsoft SQL Server"\90\DTS\Binn\MsDtsSrvrUtil.dll

Registering 32-bit version dlls
regsvr32 "C:\Program Files (x86)\Microsoft SQL Server"\90\DTS\Binn\dts.dll
regsvr32 "C:\Program Files (x86)\Microsoft SQL Server"\90\DTS\Binn\MsDtsSrvrUtil.dll

5 comments:

  1. Waouh, I had the same problem and with your solution it's worked fine now !! Thanks a lot man.

    ReplyDelete
  2. Thanks very much. the MS article seemed to read that you only needed the 64 bit DLL for running 64bit. this is not the case, thank you for your article this has corrected my problem

    ReplyDelete
  3. Wow, this happened to me on SQL Server 2005 Enterprise SP4 5057. I had to go back in and add SSIS to an existing SQL installation and ran into this annoying issue.

    ReplyDelete
  4. Thank you for sharing. It will be necessary to try to solve the problem that way. Previously, I solved this problem by simply downloaded the new dll file http://fix4dll.com/mfc110u_dll. This is for those who are lazy. Good luck.

    ReplyDelete