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:

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.

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
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

Popular Posts