Ideally DTS Packages should be upgraded to SSIS packages as we move from sql 2000 to sql 2005/sql 2008. And one of the good articles which provides information on how to upgrade DTS packages to SSIS package is written by Jason Howell
http://blogs.msdn.com/psssql/archive/2009/02/19/how-to-copy-dts-2000-packages-between-servers-and-from-sql-2000-to-sql-2005-and-sql-2008.aspx
However due to compatibility reasons or some other reasons customer may still want to use DTS packages in Sql 2005/Sql 2008. It is possible to Design,modify and execute DTS packages with Legacy components of sql 2005/sql 2008 SSMS.
However we have seen that many of our Customers face errors such as listed below while opening a DTS package with SSMS in sql 2008.
TITLE: Object Explorer ------------------------------
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)
------------------------------ BUTTONS:
OK ------------------------------
OR
The DTS host failed to load or save the package properly
and in this blog I am going to mention some of the steps which we need to take so as to be able to open the DTS package successfully with sql 2008 SSMS.
There are 6 Steps that could help you resolve this issue. We suggest you test the package after completing every steps and need not follow the rest if its resolved
1) Install Sql Server 2000 DTS Designer Components using the following link
http://www.microsoft.com/downloads/details.aspx?FamilyID=536fd7d5-013f-49bc-9fc7-77dede4bb075&displaylang=en
2) Install Sql Server 2005 backward compatibility components available with Microsoft SQL Server 2008 Feature Pack using the following link depending (x86,x64 and IA64)
http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en
3) We need to change the Environment Variable for the Server so that c:\Program files(x86)\Microsoft Sql Server\80\Tools\Binn is ahead and all the sql 2008 paths comes at the end and we can verify it by opening the command prompt and firing the command “path”
C:\>path
PATH=C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\PROGRA~1\CA\SHARED~1\SCANEN~1;C:\Program Files\CA\eTrust Antivirus;C:\Program Files (x86)\Windows Imaging\;C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\binn\;C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\
If you are not able to change the path Environment variable you can follow the following steps
Manually Copy the following files
%Program Files%\Microsoft SQL Server\80\Tools\Binn\semsfc.dll %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlgui.dll %Program Files%\Microsoft SQL Server\80\Tools\Binn\sqlsvc.dll %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\semsfc.rll %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlgui.rll %Program Files%\Microsoft SQL Server\80\Tools\Binn\Resources\%lang_id%\sqlsvc.rll
To the following destination respectively
%Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\semsfc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlgui.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\sqlsvc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\semsfc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlgui.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\%lang_id%\sqlsvc.dll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\semsfc.rll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlgui.rll %Program Files%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
To make the DTS designer work under BIDS for SQL Server 2008, user will need to manually copy the following files
%Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\semsfc.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlgui.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\sqlsvc.dll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\semsfc.rll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlgui.rll %Program Files%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id%\sqlsvc.rll
Reference: http://msdn.microsoft.com/en-us/library/ms143755.aspx
The 5th step should be followed only if all the above step does not work.
5) If the DTS packages are created from the servers which has comctl32.dll (located in C:\Wndows) older than 5.82.2900.2180 then replace the stardds.dll located in c:\Program Files\Microsoft Sql server\80\Tools\Binn (c:\Program Files(x86) Microsoft Sql server\80\Tools\Binn in case of x64 bit server) with the stardds.dll with File Version 2000.80.2151.0, Size 172KB, Date Modified 3/29/2006 5:08AM.
If you need this dll please send an email to us
Note: This post should not be treated as the Microsoft’s Recommendation or Resolution to the problem, it is only a workaround which worked in our environment and so we would like to share it.
Parikshit Savjani SE, Microsoft Sql Server.
Reviewed By
Levi Justus Technical Lead, Microsoft Sql Server
And
Karthick Krishnamurthy Technical Lead, Microsoft Sql Server
Hi,
After implementing all steps (the fifth as well) I couldn't make it works. The message:"The SQL Server 2000 DTS designer components are required to edit packages..." is shown after choosing a package and the designer windows remains empty.
The environment: SQL Server 2008 Standard Edition on Windows 2008 R2 64-bit, Virtual, server language: English (1033) but Region set to locale Id: Swedish (1053).
Regards,
Ireneus Pisanko
BI developer, Sweden
Thanks Buddy... It solved my problem. :)
This solved the problem! Thanks so much
Thanks a lot for posting this! It resolved my problem too.
Point 4 - Repair worked for me.. Thanks a lot :-)
ah.. thanx for the hint with the PATH Environment Variable..
Parikshit , thanx so much!
I've been struggling with this !@#$%$##@! for DAYS!
All the websites only refer to the DLLs and RLLs and to the 2 MSIs.
What saved me was the stardds.dll!
Have a beautiful life.
Gil Gordon
Israel
Thanks for this - re-ordering the path environment variable is the key that worked for me.
Thank you so much for this!
Well, thank you very much! I've been stucked for almost a week on this. Thanks for the hint on step 4!
can send me the link for download the dll?
[email protected]
Thanks
An excellent thread that works, even today! Had been blissfully running SQL 8 R2 on my XP machine with old DTS. Then I got this new Win 7 64-bit machine and it seemed great, super-fast, more storage, SSD....until I tried to access my DTS packages. The instructions here to copy the files from 8.0 to 10.0 did the trick. Thanks!
I repeated above fix till step 4 - nothing is working for me.
Could you please send me the DLL - I have Windows 7 SP1.
I am keep getting following error.
TITLE: Object Explorer
------------------------------
BUTTONS:
OK
Thanks, I saved my day !
Can you send me the link for download the stardds.dll ?