How to Edit DTS Packages in SQL Server 2008 R2
I’m on the verge of migrating a few DTS packages from a legacy application, there’s no point in re-writing them since the application will be phased out within the next 6-12 months so I figure that if I can migrate them ‘as is’ I’ll save myself some time and a few headaches. SQL Server Management Studio (SSMS) provides a menu option under Management to Open, Import or Migrate DTS packages…
Initially if you try to use these features this you may receive the error:
“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)”
To get around this you’ll need to follow a couple of steps:
- Install the Microsoft SQL Server 2005 Backward Compatibility Components – you can get these as part of the Microsoft SQL Server 2008 Feature Pack (for some reason it’s not included in the 2008 R2 Feature Pack). [x86] [x64] [ia64].
- Install the Microsoft SQL Server 2000 DTS Designer Components – since DTS support was depracated in SQL Server 2008 you have to get these from the Microsoft SQL Server 2005 Feature Pack. [SQLServer2005_DTS.msi]
- Ensure that the PATH Environment Variable lists the SQL 2000 directory before the 2008 R2 one – I have to thank Ramoji Ryali’s Fundas for this solution, the correct version of sqlgui.dll will be used as long as:”C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\”
is before:
“C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\”
After that, everything seemed to work fine – I did happen to log off and log back on again at some point so I’m not 100% sure if that was required or whether it was just coincidence – I definitely did not have to reboot though.
Tried everything you said and still not working for R2 says I need to install web componets which I already did
Hi Sean,
I’m not aware that web components are required at all for DTS, which makes me curious as to why you were asked to install them in the first place. If I could reproduce the error I would help but it was fine for me, you could try Twitter – tag your tweet with #sqlhelp and someone from the community will more than likely get back to you.
Regards, Ash.
I followed described 3 steps. But It is not working under Windows7. Form DTS 2000 Package Designer is opening already but with same error message “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)”
I tried all the steps…but it gives same message.
I tried (i miss the 3rd step), it works thanks a lot!
the machine is a windows server 2008r2
ciao
Leo
I was having the same problem. Only the solution mentioned in below link worked.
Thank you soooooooo much to the author
http://blogs.msdn.com/b/sqlserverfaq/archive/2009/07/09/error-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-ssms.aspx
Worked for me. Thanks!
I tried all the steps…but it still did not work.
It is working for me.
Thanks a lot for your support.
If anyone facing the problem please do the below setps.
1) re-install the Backward Compatibility Components base on the OS (32bit or 64bit)
2)re-install the Microsoft SQL Server 2000 DTS Designer Components
3)Change the path as per above
4)restart the computer
5)check whether there any third part component installed on the dts if so installed that
Thanks a lot, mate. You saved me a lot of grief!!!
Much appreciated.
will this let me modify my existing dts packages from within management studio. I read in a post from(http://forloveofsoftware.blogspot.com/2007/02/editing-sql-server-2000s-dts-packages.html) I need to have business intelligence studio to edit it.
WOW! Thanks so much! 3) Change the path worked! Be sure and click on the link in 3) ” Ramoji Ryali’s Fundas” to get more detailed information on how to change the path.
THANKS!!!
thank u so much! changing the path worked
i was losing my mind trying to get this to work. cheers!!
Thank you! Perfect!
Perfect. Works for me…
Thank you!! You saved me a lot of time and agony!
I followed evrything in the above thread, but i am not able to get .DTSX option in the ‘OPen Package File’ Window
. Please help. do i need to install any other version of BC or MS Designer components ?
Thank you so much for your posting, esp pt #3! Exactly what I needed!
thanks! in 64-bit operating system, the step 3 is worked, only “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\”
is before:
“C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\”.
but my pc is 32-bit, so “C:\Program Files\Microsoft SQL Server\80\Tools\Binn\”
is before:
“C:\Program Files\Microsoft SQL Server\100\Tools\Binn\”.
Thankyouthankyouthankyou for the PATH tip! It saved my day!
Hi,
what path needs to change in env variable?
currently its 80\tools\binn is there.
should it be 100\…?
Both should be in there but it’s the order that matters, some of this will of course depend on any particular install options you’ve chosen so I would advise double-checking the whole path variable. Cutting and pasting out into a text editor is also useful.
Thank you very much! The path step solved it! I’m running SQL Server 2008 R2 (management studio) on Windows 7 enterprise 64-bit. (mgmt studio is 32-bit, though).
Many thanks for this!