How to open DTS packages in SQL Server 2008?

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.

Comments

• Anonymous
  March 02, 2009
  Thank you for this.. Works like a champ now!

• Anonymous
  March 16, 2009
  The comment has been removed

• Anonymous
  March 25, 2009
  The comment has been removed

• Anonymous
  April 17, 2009
  Would have never thought...Thanks!

• Anonymous
  May 11, 2009
  Seems to Work fine on Windows Server 2008 x64. Thank you very much!

• Anonymous
  July 27, 2009
  The comment has been removed

• Anonymous
  August 13, 2009
  Thank you Baris.  I got it now

• Anonymous
  October 14, 2009
  The comment has been removed

• Anonymous
  November 16, 2009
  Worked like a champ for me on SQL Server 2005 and 2008, 32 bit version. Thanks for posting the solution!

• Anonymous
  January 03, 2010
  Worked great on SQL Server 2008! Thanks for the post!

• Anonymous
  January 04, 2010
  Thanks a lot dear, I spent whole day for it but after wards I found your Funda!!!   its really great.

• Anonymous
  February 09, 2010
  The comment has been removed

• Anonymous
  February 23, 2010
  Thanks for the info. Worked like a charm !

• Anonymous
  March 09, 2010
  The comment has been removed

• Anonymous
  March 15, 2010
  Thank you for posting this.  Very helpful!

• Anonymous
  March 19, 2010
  Hi Guys, I'm having issue with Windows 2008 / SQL Server 2008 R2 CTP. I've tried all of the above fundas but nothing seem to work. Any more ideas please?

• Anonymous
  April 29, 2010
  Works excellent! Thanks for the info, no where i can see this info even with Microsoft

• Anonymous
  May 06, 2010
  DTS Worked great for SQL Server2008- thanks, <a href="http://www.sqlservermanagementstudio.net">MS SQL Server 2008 Management Studio</a> Great job.

• Anonymous
  May 12, 2010
  The comment has been removed

• Anonymous
  May 13, 2010
  You just did a greate job. I had waisted a lot of time exploring this iss, when i came to this, got resolved. thx man.

• Anonymous
  June 02, 2010
  Found the answer I was looking for for two days.. Great thanks. SA

• Anonymous
  August 04, 2010
  Awesome! worked great for me.

• Anonymous
  January 27, 2011
  Baris, you are a life saver! I checked your links and dowloaded the file with sceptism (having spent the best part of a day tweaking the installation files on SQL folers!) but it worked like a charm for me on Windows Server 2008 x64 and SQL Server 2008 32 bit. A great many thanks mate!

• Anonymous
  March 21, 2011
  Thanks for the article Ramoji. For people who are still having issues making this work with SQL Server 2008, please also look at the below link. msdn.microsoft.com/.../ms143755%28v=SQL.100%29.aspx Following the steps in this article plus doing the copy mentioned in the above link @ "To ensure that the DTS designer can be used in SQL Server Management Studio" worked for me. --Raghav

• Anonymous
  August 14, 2011
  Thank you for this information. It works fine now!

• Anonymous
  February 16, 2014
  Excellent, brief description.  Worked perfectly!  Thank you.

• Anonymous
  May 29, 2014
  Another thanks!  Solved the problem.  Using SQL 2005.

• Anonymous
  October 20, 2014
  MY HERO!!!!! Worked wonderfully!! THANK YOU!!!

• Anonymous
  March 05, 2015
  Thanks so much ....this saved me a lot of trouble !

• Anonymous
  April 15, 2015
  It worked on both SQL 2005 and 2008. Thanks a lot! There's only one problem, I couldn't make it work with both instaled in the same machine at the same time.

• Anonymous
  February 03, 2016
  Wonderful - Thank you SO much ! Working perfectly

• Anonymous
  February 22, 2016
  Thanks man. This article helped me a lot!

• Anonymous
  February 29, 2016
  Nice.. It fixed the problem. Thanks a lot!! :)