Microsoft – SQL Server – SQL Server Management Studio – Error Message – “Retrieving the COM class factory for component with CLSID {10030000-EB1C-11CF-AE6E-00AA004A34D5} failed due to the following error: 80040154. (Microsoft.SqlServer.DtsObjectExplorerUI)”

Steps to recreate

  • Launch Management Studio (SQL Server Management Studio Version 2008/R2)
  • Connect to SQL Server Instance (in our case version 2000)
  • Navigate to the Instance’s Management \ Legacy \ Data Transformation Services node
  • Select one of the listed packages
  • Right click on your selection and from the drop-down menu, select the “Export..” menu item

ListDTSPackages

The error Message reads:

Textual:

TITLE: Open DTS 2000 Package
——————————

Retrieving the COM class factory for component with CLSID {10030000-EB1C-11CF-AE6E-00AA004A34D5} failed due to the following error: 80040154. (Microsoft.SqlServer.DtsObjectExplorerUI)

Image:

RetrieveTheComClassFactory

If you click on the “Show technical details” button, you will see:

AdvancedInformation

Remediation Steps

Remediation Steps – Download Location

Microsoft SQL Server 2008 Service Pack 2 Feature Pack

Remediation Steps – Install Downloaded App

Download “Microsoft SQL Server 2008 Service Pack 2 Feature Pack” from  http://www.microsoft.com/en-us/download/details.aspx?id=6375
The first time we got an error message about unsupported OSes:

Image:

UnsupportedOS

If same happens to you, please return back to http://www.microsoft.com/en-us/download/details.aspx?id=6375 and click on “Install Instruction” button.

InstallInstuctions

and navigate to the “Microsoft SQL Server 2005 Backward Compatibility Components” section.

Within that section, based on the bitness of your client OS, choose the OS specific version.

Microsoft SQL Server 2005 Backward Compatibility Components

In our case, our OS is 64-bit and so we chose to download the x64 (SQLServer2005_BC_x64.msi).

Once downloaded we followed the well presented MS Windows Installation Steps.

Installation GUI Includes:

MicrosoftSQLServerv2005BackwardCompatibility_InstallationPath

Post Install Steps

Restart Management Studio processes.

And, retry export steps:

  • Connect to SQL Server Instance (in our case version 2000)
  • Navigate to the Instance’s Management \ Legacy \ Data Transformation Services node
  • Select one of the listed packages
  • Right click on your selection and from the drop-down menu, select the “Export..” menu item

If all is well, that is, it is everything you like about Sundays, you will see a screen that looks like the one pasted below:

SelectPackage

One of the things I like about DTS packages is that journaling is built in.

Technical: Microsoft – SQL Server – Document DTS Packages

Technical: Microsoft – SQL Server – Document DTS Packages

Background

Prior to version 2005, MS SQL Server mainline ETL building block was DTS (Data Transformation Services.

Starting from v2005, DTS was replaced with SSIS (SQL Server Integration Services).

Introduction

Thankfully, legacy DTS Packages are still supported in v2005 through v2008/R2.

MS SQL Server v2005+, View MSDB – DTS Packages

In SQL Server Management Studio, to view the DTS Packages please do the following:

  • Launch Microsoft SQL Server Management Studio
  • Connect to the SQL Server Instance
  • Access the SQL Node
  • Transverse the SQL Node’s — Management,  Legacy, Data Transformation Services

Document DTS Packages

Using Transact SQL, list DTS Packages



use [msdb];
go

select 
	  tblDTSPackage.name as packageName
	, tblDTSPackage.[description] as packageDescription
	, tblDTSPackage.createdate		  
	, tblDTSPackage.[owner] as packageOwner
	, tblDTSCategory.name as dtsCategory		
	, tblDTSCategory.description as dtsCategory

from   msdb.dbo.sysdtspackages tblDTSPackage

	  left outer join msdb.dbo.sysdtscategories as tblDTSCategory

		on tblDTSPackage.categoryid = tblDTSCategory.id

where  tblDTSPackage.createdate =
		(
			select max(tblDTSPackage_Inner.createdate)
			from   sysdtspackages tblDTSPackage_Inner					where  tblDTSPackage.id = tblDTSPackage_Inner.id
		) 
order by 
	tblDTSPackage.name	

Additional Software

If you are running Microsoft SQL Server Management Studio v2005, you might need additional software as documented in “Integration Services download” ( http://msdn.microsoft.com/en-us/sqlserver/aa336314.aspx )

  • Microsoft SQL Server 2000 DTS Designer Components
  • Microsoft SQL Server 2005 Backward Compatibility Components

 

Ongoing Support

Starting from MS SQL Server v2012, Microsoft no longer supports the DTS run-time.

Discontinued Integration Services Functionality in SQL Server 2012
http://technet.microsoft.com/en-us/library/bb500429.aspx