Wednesday, September 16, 2009

"no such interface supported" and SSIS

When we try to connect to SQL Server 2005 Integration services using SQL Server
Management Studio, we get an error -

Connect to SSIS Service on machine failed:
No such interface supported


Full technical Details:
=================

Cannot connect to MySSISServer.

===================================

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

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

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo,
Request request)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.Valida
teConnection(UIConnectionInfo ci, IServerType server)
at
Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Connect to SSIS Service on machine "MySSISServer" failed:
No such interface supported
.


------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server,
String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object
ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo,
Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo,
Request request)

===================================

Connect to SSIS Service on machine "MySSISServer" failed:
No such interface supported
.


------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String
bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server,
String& serverVersion)


Solution:
=========================
Root Cause:
==========
We may get the above error if the DLL MsDtsSrvrUtil.dll, located under Installation Folder>\90\DTS\Binn\MsDtsSrvrUtil.dll is not Registered successfully
on the machine (SSIS Server).

The most common cause of this error is -

SQL Server 2005 Service pack or CU setup failed to re-register the above DLL (we
can review the setup logs to verify)

During setup of a SQL Server 2005 Service pack or CU, setup tries to unregister and
re-register relevant DLLs on the machine and we have seen certain cases when this
DLL (and other DLLs) was not re-registered successfully.


Resolution:
==========

Re-register the DLL MsDtsSrvrUtil.dll and DTS.dll like below -

regsvr32.exe "\90\DTS\Binn\MsDtsSrvrUtil.dll"

We don't require SSIS service restart or to re-open SSMS. After re-registering it
starts working.

4 comments:

SEO Professional said...

Wow: really great post thanks for sharing this types of nice Post
SEO India

Unknown said...

Thanks..This was really helpful. The key was registering both dll's. I registered the dts.dll only but with no success. Thanks Again.

Unknown said...

Nice blog. Keep updating...

Search Engine Optimization Company in Noida

nachamkadinger said...

Casino & Sportsbook | Jackson - KTMH
Casino & Sportsbook at Jackson's 시흥 출장마사지 Casino 김천 출장안마 & Hotel is located in Jackson. 의왕 출장마사지 We have 2,542 광양 출장안마 slot 세종특별자치 출장안마 machines, 392 table games, and a poker room.