The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Getting legacy DTS Designer working with Management Studio for Pablo Picasso’s

Posted by Sudarshan Narasimhan on September 16, 2011


There are many of you SQL Developers and wizards out there still working on legacy DTS packages. Of course, we cannot live without SSMS (SQL Management Studio) and thus try to integrate the legacy SQL 2000 DTS Designer into it. But, life is not that easy and the entire procedure to get DTS Designer/Runtime working on SQL 2005/2008/R2 Management studio is quite cumbersome.

While I was working on setting this up myself, learnt quite a few tricks which I what this blog is going to talk about. At the end of this you should be able to both import, design and execute legacy SQL 2000 DTS packages (not SSIS) on a machine that has SQL 2005+ Management Studio installed. Read on and let free your picassonian designer skills with DTS packages.

Getting Started
First off, here is the link to the official documentation that talks about the support for SQL 2000 DTS packages on SQL 2008 R2. There are 2 types

1. Run-time support  (meaning I can execute DTS packages using dtsrun.exe)
2. Design-time support (meaning I can import, open & design DTS packages in SSMS)

How to: Install Support for Data Transformation Services Packages
http://msdn.microsoft.com/en-us/library/ms143755.aspx

Read the above article to get an understanding of what is possible and what is not (Don’t start/install anything yet) as this process can be quite tricky. My friends @ SQLServerFAQ have a nice post on this which is also something I would recommend reading here.

Ready, Set, GO!

 

Step-by-Step Instructions

1. You would need to install a few additional components, so please download the following packages and keep them ready.

a. Microsoft SQL Server 2000 DTS Designer Components
http://www.microsoft.com/download/en/details.aspx?id=11988

Download the package SQLServer2005_DTS.msi

b. SQL Server 2005 Backward Compatibility Components

Depending on the version of your SQL Server Management Studio (SSMS) you will have to download the corresponding package

SQL 2005 – http://www.microsoft.com/download/en/details.aspx?id=15748

SQL 2008/R2 – http://www.microsoft.com/download/en/details.aspx?id=8824

Download the package SQLServer2005_BC.msi. If you have a 64-bit machine, then you would need the x64 package because the x86 package will not let you install and you will get this error.

image

c. Make sure when you had installed Management Studio that you have chosen “Management Tools – Complete” during the setup for SQL 2008.

 

2. Install both the above packages in the same order as listed above. Once this is installed you will see that the following paths containing some DLL’s

C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn \
C:\Program Files\Microsoft SQL Server\80\Tools\Binn\

3. Now, you need to edit the system PATH to ensure that the path for “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\” comes before the ..\90\Tools or ..\100\Tools folders. You can do this by going to My Computer –> Properties –> Advanced –> Environment Variables and editing the value for "PATH” as shown below.

image

Here is a sample path value from my working machine.

PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Windows\System32\WindowsPowerShell\v1.0\

4. Okay, now comes the manual part. This step is the same as given in the MSDN article.

 

32-bit Machine (x86)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

64-bit Machine (x64)

a. Copy the files, SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\ folder

b. Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, from the %ProgramFiles(x86)%\Microsoft SQL Server\80\Tools\Binn\Resources\ folder to the %ProgramFiles(x86)%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033\ folder (for English locale). For example, for U.S. English, the lang_id subfolder will be "1033".

On a system with default settings,

%ProgramFiles(x86)% –> C:\Program Files (x86)\
%ProgramFiles% –> C:\Program Files\

 

5. After this is done I would suggest a reboot of the machine to ensure that if any pending file renames are present, they will be completed on a system restart. This is not mandatory, but just a suggestion. If you cannot reboot, then you need to close all instances of SSMS and re-open to test opening a DTS package.

TESTING

From SQL Server Management Studio, navigate to the Legacy folder in Object Explorer as shown below

image

If all goes well, then when you open a SQL 2000 DTS package (test.dts), then you will see the legacy DTS designer open up as shown below.

image

Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,

image

Error Text

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)

If you have faithfully followed the above procedure step-by-step then you will need to perform this additional step as well on those systems where you receive the above error. This is why I have not added this step as default since it does not occur on all systems.

1. Navigate to C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn and see if there is a file called stardds.dll present there.

2. Note down the file version, date and size.

3. If your Operating System is Windows Server 2008/R2 or Windows Vista/7, then you will need to replace the file present with the one given below. This is because Windows 2008+ (including Windows 2008 R2) requires the 2005.90.4035.0 version of stardds.DLL. Windows 2003 or earlier requires the 2000.80.2151.0/2282.0 versions of stardds.dll

a. Rename the existing stardds.dll to stardds.dll.original

b. Download the version of stardds.dll that applies to your OS

For Windows 2008/2008 R2/Vista/Win7 –> Download stardds.dll.4035  from HERE
For Windows 2003/2003 R2 –> Download stardds.dll.2282  from HERE

c. Copy the downloaded DLL to the location C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\ (for 64-bit systems) OR C:\Program Files\Microsoft SQL Server\80\Tools\Binn\ (for 32 bit systems) and name it as stardds.dll

4. Close SQL Management Studio and re-open it and repeat the process of opening the DTS package and it should work now.

Caveat for DTS Packages contains Analysis Service (AS) Tasks
Apart from this, if any of the DTS packages have Analysis Services Cubes then you will still not be able to open these packages in SSMS. You will be faced with this error message

Error Source : Microsoft Data Transformation Services (DTS) Package
Error Description : Invalid class string

The backward compatibility package do not include design-time support for modifying DTS packages that contain the SQL Server 2000 Analysis Services Processing task and also do not include support for the SQL Server 2000 Data Mining Prediction Query task. So, if your DTS package has either "AS Processing Task" or "Data Mining Prediction Query task", then we cannot open/design these packages in SSMS 2008/R2

If you want to be able to do so, then you will have to install the following SQL Server 2000 components from SQL Server 2000 media

1. Analysis Manager
2. Decision Support Objects
3. Client Components

You don’t the AS Server component, just the above will suffice. Once you install these components you then did the get the following DLL’s present on the machine,

msmdtsp.dll   –> for Analysis Services Processing Task
msmdtsm.dll  –> for Data Mining Prediction Task

Close and re-open SSMS and you should be able to open the package now.

 

Phew! This was quite a long post indeed but I think I have covered most issues one could run into. If you come across any other issues, leave a comment behind and I will try and see if that can be resolved.

As always stay tuned to the TheSQLDude for more….

Advertisements

40 Responses to “Getting legacy DTS Designer working with Management Studio for Pablo Picasso’s”

  1. BartekR said

    Is there another source of stardds.dll version 2005.90.4035.0 (hotfix, servicepack, …)?

    I downloaded the library and now it works great. The key words are “If your Operating System is Windows Server 2008/R2”. That information was lacking in another sorces how to design DTS packages in SQLServerR2.

    Thanks a milion!

    • Sudarshan Narasimhan said

      Thanks Bartek. stardds.dll (DTS Runtime DLL) is only part of SQL 2000 and SQL 2005 backward compatibility packs. So it has to be in either 8.00.20XX or 9.00.XXXX builds. Depending on your service pack level, the last 4 build numbers will differ. But in case the OS is Windows 2008 or greater you will definitely need the DLL to be 9.0.XXXX series for it to work. That was the key part which isnt documented. Too minute a detail i guess, but i know a lot of people who’ve rebuilt machines cauz of this tiny detail 🙂

      • BartekR said

        Exactly – 2005.90.4035.0 did the trick. But I can’t find another source of the library than the skydrive location you wrote. Is there another place (more “official”) for it?

  2. Kishroe said

    Hi Sudarshan, you made my day…. I have been breaking my head for a day now to make this work.. tried all the documentation given in different blogs but nothing really worked. Atlast got the stardds.dll stuff corrected and it works great… Thanks a bunch Sudarshan.

  3. A. S. said

    I’ve been looking for months for a solution to edit DTS packages in SQL2008 on a 64bit machine.
    Yours was the only one working! 🙂
    Great job, many thanks for sharing!

    A.

  4. Chris said

    Thank you a 1000 times.

  5. Eduardo Goicovich said

    Hi Sudarshan,

    All works fine, but I cannot edit text annotations from the layout designer.
    In another w2k8 box, all is good
    Any suggestion?

    • rdmjrb said

      Yes Eduardo, we are having a similar issue on this one and unable to resolve on a few boxes….but either way this is a very comprehensive post. Thanks.

      • David said

        Hi There,
        I think I managed to figure out most of the above steps on my own. I found this post while trying to figure out why I can edit my Text Annotations.
        I’m running SQL 2008R2 on Win 2k8 R2
        I have a similar SQL Express machine on Windows 7 doing the same thing.

        Did anyone find a solution to this?

        Thanks
        David

  6. Saurabh said

    Hi Sudarshan,

    Thank you very much for this information specifically the dll’s for windows 7. You totally rocked .

    Thanks once again.

  7. Thank you very much for suggestion with stardds.dll.

  8. cobra21 said

    Thank you! Thank you! Thank you! and…. THANK YOU!!!

  9. Anup said

    Thanks a ton!!!

  10. veerendra said

    Thank u so much sudarshan , this is the only link which helps me .my server OS is windows 2008 R2 ,Iam trying to resolve this issue form last 2 days .I t saved me lot of time .

  11. veerendra said

    Stardds.ddll stuff resolved my issue .

  12. Virgy said

    Great post! Thank you so much for sharing 🙂

  13. Steve said

    Dude, you rock. This just saved us big time!

  14. Libor said

    Great help. Thank you a loooot

  15. Rajeev said

    Thanks for your help. It really worked. You have explained very clearly.
    Now I have opened the package in 2008.
    My next task is to open it using SSIS.
    Can you please guide me on that.

  16. pd12 said

    Able to open and migrated in sql2008 r2 – x694 thru designer 2000 but during first time when we open it gives SQLDMO.dll has not registered and when we click ok able to get thru.
    Wants to make sure its something needs to conceren or fix

  17. […] https://thesqldude.com/2011/09/16/getting-legacy-dts-designer-working-with-management-studio-for-pabl… […]

  18. Joaquin said

    Dude, I have to thank you for you help! Thank you for your kindness. after following instructions to the T, it would still not work for me, until I updated the stardds.dll file with the newer version for Win Srv 2008 R2. now I wonder if it will work with Win Svr 2012? I will find out soon.

    Thank you Sir!

    • You’re welcome Joaquin. Even I haven’t tested the stardds.dll trick on Win Server 2012. Let me find a 2012 server and test this out. Will post back the results. If you’ve tested it, do leave behind a comment on your results.

    • Arijana said

      I was not able to make it work on windows 2012 even with these instructions. Maybe I am missing something but after a couple of days I give up and now am installing a Win 2008 machine instead and then will follow these instructions on that machine… Thank you on such a great post!

  19. Héctor said

    Sudarshan, thanks very much for all your help…I have been breaking my head from last night trying with all documentation from others blogs but nothing worked..today I saw your blog and for luck I have a SQL 2005 so I could get the dll from there..after did the copy it in the new server SQL 2008 and register it, all worked ok… Thanks a lot again….I’m from Chile in Sudamerica…

  20. Vijay said

    Where is stardds.dll for Win 7 32 bit, SQL Server 2008 R2? Link doesn’t work

  21. kendra said

    you can also use http://sqltreeo.com

  22. Josh said

    Here it is, 2015 and I just stumbled upon this and the DLL you made available fixed my issue. Thanks!

  23. Carlos Gonzalez said

    Hi, i have done all step by step but i can’t see my DTS package when i import them to my server, but i can see and open it from other server, any suggestion?

  24. Amazing article Sudarn 🙂

  25. Julienne said

    Great post.

  26. Saurabh said

    cool dude..it works for me

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: