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.
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.
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
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.
Still Not Working
If things go wrong, then you will get this error when you tried to open a DTS package,
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 HEREc. 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….
The SQL Dude! 
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?
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.
Sudarshan Narasimhan said
Thanks Kishore. Glad the blog helped, stay tuned for many more SQL Server posts here…
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.
Sudarshan Narasimhan said
You’re welcome 🙂
Chris said
Thank you a 1000 times.
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
Saurabh said
Hi Sudarshan,
Thank you very much for this information specifically the dll’s for windows 7. You totally rocked .
Thanks once again.
Sudarshan Narasimhan said
Thanks Saurabh, glad the blog helped you…
Honza Pačuta said
Thank you very much for suggestion with stardds.dll.
cobra21 said
Thank you! Thank you! Thank you! and…. THANK YOU!!!
Sudarshan Narasimhan said
Thank you 🙂
Anup said
Thanks a ton!!!
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 .
Sudarshan Narasimhan said
Thanks Veerendra, glad this solved your issue with DTS Designer
veerendra said
Stardds.ddll stuff resolved my issue .
Virgy said
Great post! Thank you so much for sharing 🙂
Steve said
Dude, you rock. This just saved us big time!
Sudarshan Narasimhan said
The Dude abides 🙂
Libor said
Great help. Thank you a loooot
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.
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
Mit SQL Server Management Studio 2008 DTS Packages öffnen oder erstellen | datatipp said
[…] https://thesqldude.com/2011/09/16/getting-legacy-dts-designer-working-with-management-studio-for-pabl… […]
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!
Sudarshan Narasimhan said
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!
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…
Sudarshan Narasimhan said
You’re welcome Hector. stardds.dll is the key 🙂
Vijay said
Where is stardds.dll for Win 7 32 bit, SQL Server 2008 R2? Link doesn’t work
kendra said
you can also use http://sqltreeo.com
Josh said
Here it is, 2015 and I just stumbled upon this and the DLL you made available fixed my issue. Thanks!
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?
Karthik Krishnamurthy (KKB) said
Amazing article Sudarn 🙂
Sudarshan Narasimhan said
Thanks kkb 🙂
Julienne said
Great post.
Saurabh said
cool dude..it works for me
Eddie Teng said
I really appreciate your contribution. We solved the problem with your stardds.dll.4035. Thank you so much!!!
Wish you all the best, Sudarshan!!!!!
lemming3k said
Seems to work for resolving the DTS Designer error – however instead I’m getting a fairly generic appcrash (SSMS has stopped working) for ssms.exe when opening packages. Exception code c0000005.
Anyone else come across this?