Error: SQL Server 2000 DTS Designer components are required to edit DTS packages. (Microsoft.SqlServer.DtsObjectExplorerUI)
The error message text (partial) on the subject will be familiar for the DTS/SSIS users in SQL Server.
On the background of the issue is that when you attempt of open a DTS package for editing from SQL Server 2008 Client machine, I have seen questions within forums that few users are unable to locate the SQL Server 2000 DTS Designer Components for SQL Server 2008 version and only SQL Server 2005 are available to download. More to the extension of the above error:
TITLE: Object Explorer
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)
Recently I have gone through this error and resolved the issue on a SQL Server instance that has been installed for use on Windows Server 2003 & 2008 X64 enterprise edition. To resolve the error a fair amount of background of process is required for you to understand.
Thanks to the Microsoft Documentation team that they have done a good job in documenting the process and procedures within the Books Onine (SQL Server 2005 & 2008 versions). Such in action the instructions for installing the DTS Design components can be found from this BOL143755 link (as I don't want to refer then again it is best for you to go through). So as per the documentation process I have copied the relevent .DLL and .RLL files from the 80 (SQL 2000) version to 100 (SQL 2008) directories and then tried to open the DTS packages from the same client machine.
Again a new error has been popped up stating "The DTS host failed to save the package properly". I vaguley remember that one of the KB article refers that this error whent he following conditions are met:
- The SQL Server 2000 DTS package was last saved from an instance of SQL Server 2000 that is running on a Windows 2000-based computer.
- The SQL Server 2000 DTS package is stored in a structure storage (.dts) file.
Not only within SQL Server 2005 vresion this problem also occurs because of a compatibility issue with how icons are managed between DTS Designer in SQL Server 2000 and DTS Designer in SQL Server 2005. Talking about backward compatibility of the tools I wanted to check whether the SQL Server 2005 Backward Compatibility Components are installed or not, they aren't. For your reference you can download this SQL Server 2005 Backward Compatibility
Components from here and FYI these are updated for the SQL Server 2008 compatibility too.
I also remembered about a discussion that I have had with the PSS engineer in the past that SQL Server 2008 does not include support for "64-bit design-time or run-time" support for DTS packages. Say (you may be aware by this time) on a 64-bit computer, DTS packages, and Integration Services (SSIS) packages that run DTS packages, can run only in 32-bit mode. To run DTS packages in 32-bit mode, you have to install and use the 32-bit version of the dtexec utility. This dtexec.exe is needed if you haven't installed this 32-bit version of the utility during the client Tools or Business Intelligence Development Studio installation at setup process.
Similar to above there is also no 32-bit design-time or run-time support for DTS packages on Itanium-based operating systems. Due to this design constraint the user cannot create, view, modify, or run DTS packages on Itanium-based operating systems too. To avoid either you can download that dtexec utility from the above download link or refer to this link about installing the 32-bit version of of the Integration Services runtime utility. Also it is worth to refer about how to: Run a Package Using the DTExec Utility and finally a note about scheduling these packages. Say if you use SQL Server Agent to run the utility, SQL Server Agent automatically uses the 64-bit version of the utility. SQL Server Agent uses the registry, not the PATH environment variable, to locate the correct executable for the utility. To do this BOL refers (again) that:
To ensure that you run the 64-bit version of the utility at the command prompt, you can take one of the following actions:
- Open a Command Prompt window, change to the directory that contains the 64-bit version of the utility (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn), and then run the utility from that location.
- At the command prompt, run the utility by entering the full path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) to the 64-bit version of the utility.
- Permanently change the order of the paths in the PATH environment variable by placing the 64-bit path (<drive>:\Program Files\Microsoft SQL Server\100\DTS\Binn) before the 32-bit path (<drive>:\ Program Files(x86)\Microsoft SQL Server\100\DTS\Binn) in the variable.
So after all the checkout on the tools for backward compatibility we found that the main issue occurred due to the package_path definition within that DTS package. Documentation refers that package_path or filespec arguments of the /SQL, /DTS, or /FILE options must be enclosed in quotation marks if the path or file name contains a space. If the argument is not enclosed in quotation marks, the argument cannot contain white space. To close up this topic it is a must reference for every SQL user to go about SQL Server Backward Compatibility feature installs the DTS runtime for SQL Server 2008, however it works only on 32-bit platform. Best practices dictates that you need to migrate DTS packages to SSIS environment as and when time is available, as a final reference on this migration process refer to this Migrating DTS Packages to Integration Services link and get the SQL Server 2000 DTS Designer components too.