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

Wednesday, October 7, 2009

SQL PerfMon Counters Missing

SQL Server Performance Monitor Counters are Missing
To solve this problem follow the following steps:
Stop SQL Server Service
Open Command Prompt (Start – Run – Cmd)
Run the command: NET STOP MSSQLSERVER
Then, this question will appear: “The following services are dependent on the SQL Server (MSSQLSERVER) service. Stopping the SQL Server (MSSQLSERVER) service will also stop these services: SQL Server Agent (MSSQLSERVER). Do you want to continue this operation? (Y/N) “
Press “Y”
Open Command Prompt and get located on the BINN directory of SQL Server. Probable locations are:
c:\Program Files\Microsoft SQL Server\90\Tools\Binn
c:\Program Files\Microsoft SQL Server\80\Tools\BINN
Once located on the BINN directory, using the Command Prompt, execute the following command: lodctr sqlctr.ini
Restart your computer.

SQL Server OS Components



SQL Server OS Components diagram:-