Editing DTS Packages using SQL Management Studio 2008

Blog

At the client I’m at one of the applications I’m working on still uses SQL 2000 as the database platform.  There is a DTS package that the applications uses to import some data and it just so happened that I need to edit that package.  About two months ago I had loaded the SQL 2008 client tools on my local machine and I didn’t have the old SQL 2000 tools anymore.  I went searching around and found this blog post by Brian Knight regarding how you can get the DTS Designer for SQL 2005 to work in the 2008 management studio. 

It’s pretty simple.  I’m going to repeat Brian’s instructions here, but only because I want to be able to find them later. :)

  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 that just open up SQL Management Studio 2008 and edit DTS packages to your heart’s content.

I have to give all the credit to Brian on this, but it sure saved me a lot of time and heartache (well, some heartache…I was having to work on DTS packages).