An error occurs if you try to open a DTS package from Leagcy --> Data Transformation Services in SSMS of 2008. The message says, 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.DtsObjectExploreUI).

 

Downloading the following software from Microsoft's download center and Installing fixes this issue in most of the cases.

 

1. Microsoft SQL Server 2005 Backward compatibility

2. SQL Server 2000 DTS Designer Components

 

However, in some cases it doesn't solve the problem and the error re-appears even after installing the above software.

 

Reason for this:

SQL Server uses the library (sqlgui.dll) to open DTS package in GUI mode. This DLL comes with katmai (Located in <SQL Path>\100\Toos\Binn) also but this shouldn’t be used. The DLL that comes with SQL server 2000 (Located in in <SQL Path>\80\Toos\Binn) should be used. SQL server looks for the path of this DLL in the order in which the paths are defined in PATH environment variable. So, we need to make sure 80 comes before 100.

  

Perform the following steps to fix the issue in such situation.  Please note that this workaround should be used in Development environments only. Playing with the environment variable on Production systems might hamper some or the other application and hence avoid this in such cases.

  1. Make sure all the SQL Server Management Studio (SSMS) sessions are closed
  2. Go to  ControlPanel --> System --> Advanced --> Environment variables
  3. Update the PATH environment variable in such a way that the path “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\” appears before the “C:\Program Files\Microsoft SQL Server\100\...” paths. On the server, the folder might be Program Files X(86) instead of Program Files. Use the path accordingly.
  4. Launch SSMS and open a DTS package from Legacy à Data Transformation Services
  5. You will get some errors. Click OK. The package opens but the icons can’t be seen.
  6.  Just hover the mouse over the icons till you notice icons as white boxes
  7. Exit SSMS
  8. Again launch SSMS and open the same DTS package from Legacy à Data Transformation Services.
  9. Package gets opened properly

Sometimes steps from 4 to 8 have to be performed more than once if the error persists.