Skip to main content

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature. (Microsoft.SqlServer.DtsObjectExplorerUI) – SQL Server 2008/2008 R2 Management Studio throws error while opening a DTS package


If you try to open a DTS package in SQL 2008/2008 R2 Managgment studion, you may get an error as below which prevents you even from opening the package:

SQL Server 2000 DTS Designer components are required to edit DTS packages. Install the special Web download, "SQL Server 2000 DTS Designer Components" to use this feature.

An example screenprint of the error message is as below:


Another common error we may experience while opening DTS in SQL 2008/2008 R2 SSMS is as below:

The DTS host failed to load or save the package properly


Please follow below steps to resolve this problem and to open, design,modify and execute DTS
packages with Legacy components on SQL 2008 / 2008 R2

1. Install SQL 2000 DTS Designer Components on the machine where you are working (that is where SSMS is installed). Select SQLServer2005_DTS.msi  file from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=11988 to download this tool.

2. Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack. Select appropriate file depending on your architecture (x86,x64 and IA64) from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=16177 and intstall on the machine where you are working (that is where SSMS is installed).

3. Manually Copy following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
4. Repair Sql Server 2005 backward compatibility by reexecuting the file you downladed at Step#2 or from control panel’s add/remove program.

Note:

1. If above step does not work then most likely you need to replace stardds.dll as per Step#5 of http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx

2. If you are facing issues for opening DTS in BIDS (Buisness Intelengience Development Studio), then please refer to Step 3 at http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx for the list of files you need to copy from %Program Files%\Microsoft SQL Server\80\Tools\Binn to %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE and it’s subdirectory.

3. I tested the steps listed above and I works fine. However please understand this is a workaround suggested by Microsoft PSS at http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-sql-server-2000-dts-designer-components-are-required-to-edit-dts-packages-install-the-special-web-download-sql-server-2000-dts-designer-components-to-use-this-feature-microsoft-sqlserver-dtsobjectexplorerui-ssms.aspx but Microsoft does not support this procedure formally.

4. I do not recommend to execute these steps on a server’s management studio. You should execute these steps and work on DTS from a workstation where SQL 2008 / 2008 R2 management studio (SSMS) is installed.

5. If you are running DTS on a SQL 2005 or later server then you should consider migrating to SSIS.

Labels:

Comments

Anonymous said…
Hi

This article is excellent. But, I am copying all the SQL2000 based DTS packages to SQL 2008R2.

My question is whether should I still download SQL2005 DTS component in SQL2008R2 so that i can help to edit DTS packages?

Appreciate your help.

Tx
jay
July 2, 2012 at 1:13 PM
UC said…
Hi Jay

Thanks for your comment. If you need to edit DTS packages on the server itself, then you need to do this installation which will allow you to edit the package using Management Studio. However please note that it is always advisable to edit DTS package from a client machine so that you do not install these components/customizations on server. Hopefully this answered your query. Else please let me know if you need any specific information.

Thanks
Utsab
July 19, 2012 at 11:06 PM
Post a Comment

Popular posts from this blog

How to kill a negative SPID (like SPID -2) in SQL Server?

Rarely this scenario will arise when most likely you see this negative SPID (most likely SPID -2) is blocking other transaction causing issues. If you try to kill it using normal KILL command, it will fail reporting below error: Msg 6101, Level 16, State 1, Line 1 Process ID <SPID Number> is not a valid process ID. Choose a number between 1 and 2048 This is because of an orphaned distributed transaction ID.  Please follow below steps to kill it: Step 1: -- Find the UOW Number select req_transactionUOW from master..syslockinfo where req_spid = <SPID Number> --  <SPID Number>  is -2 most likely. Step 2: -- Copy the UOW number from Step one KILL ‘<UOW Number>’ This will kill the negative SPID resolving the issue.  However please note following points: 1. For SPID -2, you may find multiple UOW numbers. Please start killing them one by one. Typically killing first UOW will resolve the issues. (ie. will kill all UOW and release
Post a Comment
Read more

DMV/TSQL to find out basic hardware information of the SQL Server including when SQL Server started.

Please use below code: However, please be advised that it can not tell correct information around virtualization.  For example, it will show Hypervisor even if SQL runs on a physical OS where Hyper-V is on. So use this query only when you do not have sufficient access on underlying Windows Operating system to get these information directly. -- Basic hardware information for SQL Server (sys.dm_os_sys_info) /* This query is courtesy of https://sqlserverperformance.wordpress.com/. All credits goes to original author. */ SELECT cpu_count AS [Logical CPU Count] , scheduler_count , hyperthread_ratio AS [Hyperthread Ratio] , cpu_count / hyperthread_ratio AS [Physical CPU Count] , physical_memory_kb / 1024 AS [Physical Memory (MB)] , committed_kb / 1024 AS [Committed Memory (MB)] , committed_target_kb / 1024 AS [Committed Target Memory (MB)] , max_workers_count AS [Max Workers Count] , affinity_type_desc AS [Affinity Type] , sqlserver_start_time AS [
Post a Comment
Read more

‘Trace Skipped Records’ – What is this and how to resolve it while using SQL Server Profiles?

In some very rare case, you may experience a very weired message in profiler’s output as ‘Trace Skipped Records’ while you trace something on SQL Server. Screenshot of similer situation is as below: This is not an error but it comes by design of SQL Server (I believe so). When you are using SQL profiler and return data is too big to fit in the GUI (for me, it is an enormous xml), SQL Server simply prints this message and proceed to next step. Mostlikely this is to save server’s memory and performance. Although not suggested and guranteed, you can try to run a server side trace and dump data in a file which should capture all the data. However, it is strongly not recommended to run a trace on your production server from server side. Microsoft will probally document this limitation in future. More details may be found at https://connect.microsoft.com/SQLServer/feedback/details/304225/msft-edw-profiler-displays-trace-skipped-records-for-large-530kb-batch 
Post a Comment
Read more