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\”
“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.