Today one of the members of the Project DBA Team installed SQL Server Integration Services (SSIS) 2005 on one of the production servers. The installation was successful and the first thing anyone would do to confirm is connect to SSIS through SSMS. Surprise!
The error message reads “Connect to SSIS Service on machine “ServerName” failed: Error loading type library/DLL.” and there is no mention of the name of the DLL which is having issues.
The next option is to check if we are able to connect to the SSIS from a remote server. Yes, we were able to connect to this instance of SSIS from some other server. Now it was confirmed that the problem did not exist with the SSIS installation as such. It had to do something with the DLLs located on the same box.
Since SSIS is having issues, the first DLL that would come to mind is the DTS.dll. This file is usually located in C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn folder. While I was navigating to this folder, I observed that a folder named 100 was also residing in the same location as that of 90 folder. This hinted that someone had tried some of the components of SQL Server 2008 and it could have possibly messed up some of the files related to SQL Server 2005.
Hence I planned to register the DTS.dll again. Just opened the DTS.dll using C:\WINDOWS\SYSTEM32\regsvr32.exe in Windows Explorer and it said “Registered successfully”. This can also be done using the command line regsvr32.exe.
Again opened up SSMS and as predicted we were able to connect to the Integration Services.