Ramblings of an IT Junkie

SQL SSMS 2008 on x64 Operating System December 11, 2009

I know it’s been a while since my last post, and I apologize for that. Lots been going on in my life, and I’ve been slipping lately. I’m hoping to finally be able to post information that I wanted to do when I started. I’m hoping that from now on, I’ll be able to get some good quality information out there on a daily to bi-daily basis.

With that said, on with the show:

Have you ever used SQL Server Management Studio on an x64 system and try to manage legacy DTS jobs? If so, then unless you’ve done the voodoo uneedtoo, you’ll run into the headaches and heartaches of trying to solve this capracious little problem without pullling out all your hair (I’m bald now…trust me).

So I’ve decided to make one succinct location. In this walk-through we’ll be installing the DTS Run-Time components and setting them up to work properly on your x64 operating system. I recommend having done a slip-stream install of SQL Server 2008.

Obviously by now you have instaled SSMS 2008 on your computer. If you did not install “Backwards Compatibility” as well as “Integration Services”. The Integration Services install the ActiveX Script Task and the DTS Package Migration Wizard. You can download and install the current Backwards Compatibility Installer from SQLServer2005_BC_x64. Or, you can install it from the setup for SQL Server 2008.

Once you have installed this file, you’ll need to get the SQL Server 2000 DTS Design Components. These can be downloaded from here: SQLServer2005_DTS but understand, this file is ONLY available for x86 platforms. But it will install just fine on your new x64 box, and we want the 32-bit components anyway.

Once this file has been installed, we need to copy some of the .dlls from the x86 portion to the x64 portion.

You need to copy: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL from %ProgramFiles (x86)%\Microsoft SQL Server\80\Tools\Binn to %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE
You’ll also need to copy: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL from %ProgramFiles (x86)%\Microsoft SQL Server\80\Tools\Binn\Resources to %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% %lang_id% is the language id of your installed locale. For English user this is 1033.

Here are some quick commands to do the above tasks for you automatically:

COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SEMSFC.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SQLGUI.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\SQLSVC.DLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE”

COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SEMSFC.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SQLGUI.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″
COPY “C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\Resources\1033\SQLSVC.RLL” “C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\1033″

Once you restart SSMS 2k8, you should be abe to open, execute, and modify legacy DTS packages.

I wish I could take credit for this helpful article, but much of the material, and links are from the MSDN site located here: How to: Install Support for Data Transformation Services Packages

I only recently found this article on MSDN helpful, as much of the instructions on there were gibberish until the past 3 months, and the locations they provided originally were incorrect for the locations of the dll and rll files.

Enjoy and happy SQL’ing!