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 (33)

  1. Matt says:

    Thank you for this.. Works like a champ now!

  2. Baris says:

    I am having this issue on a Vista Dev Box (aka my laptop) and I’ve repeated steps 4-8 many times to no avail. I do get the Package Designer window though (after getting the error message).  Any ideas?

  3. Baris says:

    Appear to have found a fix. Just a quick update, in case someone finds themselves in the boat I’m in.

    If you’re running 32-bit Vista with SQL Server 2008 SSMS, this may help you. Basically Steps 4-8 did not work for me. I could not pull up a non-blank DTS package window.

    The issue appears to be related to DEP (Data Execution Prevention).

    Relevant explanation can be found here: http://support.microsoft.com/kb/962874

    That page links to an SP3 version of the DTS Designer Components, which fix the issue here: http://www.microsoft.com/downloads/details.aspx?FamilyID=536FD7D5-013F-49BC-9FC7-77DEDE4BB075&displaylang=en

  4. Lee Everest says:

    Would have never thought…Thanks!

  5. David A. says:

    Seems to Work fine on Windows Server 2008 x64.

    Thank you very much!

  6. Kutty says:

    What Baris said about the SP3 thing just made my day… After 3 weeks of struggling with BC and DTS components taken from several feature packs.

  7. Thank you Baris.  I got it now

  8. yohis says:

    HI GUYS

    I HAVE THE SAME PROBLEM, MY SO IS WINDOWS SERVER 2003 I WANT TO USE MI DTS (SQLSERVER 2000) IN SQLSERVER 2005, I INSTALLED DTS DESIGNED COMPONENTS & BACKWARD COMPATIBILITY COMPONENTS, I DID THE FIST STEPS BUT DOESN´T WORK… I DON´T KNOW WHAT HAPPEND WITH THIS ISSUE…

    CAN YOU HELP ME, PLEASE??

    THANKS

  9. FLEX DEV says:

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

  10. Rick says:

    Worked great on SQL Server 2008! Thanks for the post!

  11. Ashish Porwal says:

    Thanks a lot dear, I spent whole day for it but after wards I found your Funda!!!  

    its really great.

  12. Ken K. says:

    Spent most of the day on this.  Going home with a clear head.  THANKS!!!

  13. Krishna says:

    Thanks for the info. Worked like a charm !

  14. BJ says:

    This worked great!  I couldn't find anything anywhere on Microsoft's website.  Thank you!!!

    BJ

  15. C. Akers says:

    Thank you for posting this.  Very helpful!

  16. C Shekar says:

    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?

  17. Vundavilli says:

    Works excellent! Thanks for the info, no where i can see this info even with Microsoft

  18. MCSD says:

    DTS Worked great for SQL Server2008- thanks,

    <a href="http://www.sqlservermanagementstudio.net">MS SQL Server 2008 Management Studio</a>

    Great job.

  19. Kevin Lewis says:

    Thank you very very much for this.. I'd done it once before but had forgotten… damn environmental variables!!

    Thank you thank you thank you!!

    Kevin

  20. Sayyaz says:

    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.

  21. S. Akyol says:

    Found the answer I was looking for for two days.. Great thanks.

    SA

  22. HN says:

    Awesome! worked great for me.

  23. Nick says:

    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!

  24. Raghav says:

    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

  25. stephan says:

    Thank you for this information. It works fine now!

  26. Allen Underwood says:

    Excellent, brief description.  Worked perfectly!  Thank you.

  27. JerryO says:

    Another thanks!  Solved the problem.  Using SQL 2005.

  28. AprilG says:

    MY HERO!!!!! Worked wonderfully!! THANK YOU!!!

  29. NP says:

    Thanks so much ….this saved me a lot of trouble !

  30. Waldir says:

    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.

  31. Julien says:

    Wonderful – Thank you SO much ! Working perfectly

  32. Aziz says:

    Thanks man. This article helped me a lot!

  33. Arun Nivas says:

    Nice.. It fixed the problem. Thanks a lot!! 🙂

Skip to main content