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
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).
%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.
Comments
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
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