posted 11/7/2009 by BrianKnight - Views: [6069]
One of the specialties of Pragmatic Works is upgrading DTS packages to SSIS but we were recently quite frustrated when we tried to use the advertised DTS Designer inside SQL Server 2008. The error you’ll receive is the following (even after you install the components).
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)
We found a series of bugs trying to open a DTS package in the DTS Designer for Management Studio in SQL Server 2008. For some reason, Microsoft only developed a SQL Server 2005 flavor of the installer that sets up the designer. So, to install DTS Designer for SQL Server 2008, you must follow the below steps:
1. Install the 2005 DTS Designer from the following location: X86 Package. If you install the earlier build of the DTS Designer which the Microsoft documentation points to, it will not work unfortunately.
2. This installs the DTS Designer into the 2005 directories so you’ll then need to copy the files over to the 2008 directories. Make sure Management Studio is closed when you run the following commands (we create a batch file to do this typically). Make sure you also replace the path with your own drive and path:
COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\semsfc.dll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\ COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\sqlgui.dll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\ COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\sqlsvc.dll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\ COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\Resources\1033\semsfc.rll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033 COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\Resources\1033\sqlgui.rll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033 COPY C:\"Program Files"\"Microsoft SQL Server"\80\Tools\Binn\Resources\1033\sqlsvc.rll C:\"Program Files"\"Microsoft SQL Server"\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033
After you open Management Studio again, you should be working. I wish it was simpler but hopefully this post saves you the time that spent on it.