Apr
28
2008

DTS support in SQL Server 2008 "Katmai"

[REVISED WITH ADDITIONAL INFORMATION SAT 5/24/08, MON 5/27/08, MON 10/13/08]

It may seem ironic to start a new blog by talking about old stuff. But we all loved DTS in our youth, and it’s been a popular subject in my Inbox lately.

Run-time support for DTS

The good news for DTS users is that DTS receives the same degree of support in SQL Server 2008 “Katmai” as in SQL Server 2005 “Yukon”. The bad news is that it’s less convenient (in Katmai) to install that support.

Run-time support for DTS is part of the “backward compatibility” Windows Installer package (MSI file) that also includes DMO. In Yukon, selecting almost SQL Server feature to install during Setup also caused the backward compatibility MSI to be installed, with DTS run-time support. In Katmai, DTS run-time support is no longer installed by Setup. However the MSI will still be on the CD. So to install run-time support for DTS in Katmai, you have to:

1.       Search your Katmai CD for the correct version (x86, etc) of the backward compatibility MSI, SQLServer2005_BC*.msi; or,

2.       Download it from the Katmai Feature Pack page.

Upgrading from Yukon to Katmai should leave the previously-installed DTS run-time support intact. (At least, Setup doesn’t take any steps to remove it.)

<added>

Several components that may be considered part of DTS support -- including the ActiveX Script task and the DTS Package Migration Wizard -- are installed when you select Integration Services for installation. However, these components are not fully functional -- or, more bluntly, don't work -- unless you also manually install the DTS run-time support, as described in this post.

 The Execute DTS 2000 Package task is installed only by the "Client Tools Backward Compatibility" option on the Feature Selection page of Setup.

Therefore, to install SQL Server 2008 Integration Services with all the features of DTS support, you have to install:

  • Integration Services during Setup, for the ActiveX Script task and the DTS Package Migration Wizard, although they are not fully functional until you also install the Backward Compatibility MSI.
  • Client Tools Backward Compatibility during Setup, for the Execute DTS 2000 Package task, although I suspect that is it also not fully functional until you also install the Backward Compatibility MSI.
  • The Backward Compatibility installer package manually after Setup, as described above, for the DTS runtime, and whatever the additional missing pieces are (if any) that make the previous pieces fully functional.

</added>

Design-time support for DTS

The DTS designer for use inside the Katmai tools is not being upgraded for Katmai. (However the team has fixed an issue that caused problems on Vista.) To avoid misunderstandings, we’re going to re-release the DTS designer on the SQL Server 2005 Feature Pack page where it lives now. That is, you won’t find it on the SQL Server 2008 Feature Pack page. However we don’t know when we’ll have a window to release an updated MSI on that page…possibly not until there’s a Service Pack 3 for Yukon.

We also explored one of the causes for the recurring prompt to download and install the designer, when you’ve already done so – a conflict with supporting DLLs of the same name, but a later version, due to the order of the PATH. We don’t see anything breaking when we swap things back the other way in the PATH, and put %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn in front of %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn. However the safer solution is to copy the SQL Server 2000 DLLs to the folders where the DTS designer can find them without relying on the PATH.

1.    To ensure that the DTS designer can be used in Management Studio:

a.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE folder: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL.

b.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Resources\%lang_id% folder: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL. For US English, for example, the lang_id subfolder will be "1033".

2.    To ensure that the DTS designer can be used in BIDS:

a.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE folder: SEMSFC.DLL, SQLGUI.DLL, and SQLSVC.DLL.

b.    Copy the following files from the %ProgramFiles%\Microsoft SQL Server\80\Tools\Binn\Resources folder to the %ProgramFiles%\Microsoft Visual Studio 9.0\Common7\IDE\Resources\%lang_id% folder: SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL. For US English, for example, the lang_id subfolder will be "1033".

For the RLL resource files mentioned in Step b of the 2 steps above, please note that the original version of this posting (until 10/13/08) did not correctly identify the destination folder. The RLL files have to be placed in the locale-specific subfolder, not in the "Resources" folder itself. For US English, for example, this folder will be: ...Common7\IDE\Resources\1033.

 

Comments (7) -

Lon Ramsey

I appreciate the correction to msdn.microsoft.com/en-us/library/ms143755.aspx">msdn.microsoft.com/.../ms143755.aspx adding the language subdirectory.  I was able to get the DTS designer to work on Windows XP but not Server 2008.


dougbert

The DTS Designer was not designed for or tested on Vista and Server 2008, as you might expect. I believe there are some issues on Vista which are probably carried over now to Server 2008. The SSIS product team is investigating.


Lon Ramsey

Have you heard of any progress for Windows Server 2008 support of DTS?


dougbert

Lon,


If you're talking about RUNNING DTS packages, I am not aware of any issues on Windows Server 2008.


If you're talking about DESIGNING DTS packages in the downloadable DTS package designer, then there is a known issue on Vista that also carries over to Server 2008. This has been fixed for SQL Server 2005 Service Pack 3, and will be released (that is, the updated download of the DTS designer) on the SQL Server 2005 (not 2008) Feature Pack Web page soon. The tentative release date for SP3 is next Monday, December 15th. That date could shift, or the Feature Pack updates might lag the SP itself by a couple days.


-Doug


Lon Ramsey

Thank you.  I have SQL 2005 DTS designer working.  I will be looking for the SQL 2008 DTS designer updates for Server 2008.


Brian Cidern

I've been able to get the DTS designer to work fine in SSMS on Windows 2008 Server.  However, I cannot get it to work within BIDS.


Package migrated.  Component left as Exectue DTS2000 package object. Double-click to bring up the task editor.  Clicking the button "Edit Package" throws the error "SQL Server 2000 DTS Designer components are required to edit DTS packages..."


Have checked, double-checked and triple-checked the file locations for the DLL's and RLL's.  Even tried a few other locations.  Verified file versions, and have even re-installed the MSI's.  Still no luck.


Anybody?


dougbert

Brian,


The problems that I described on this page are on the only problems that I'm familiar with. If you've checked the file locations, including my belated correction for the RLLs, then the SSIS forum on MSDN may be your best resource next for tech support: social.msdn.microsoft.com/.../">social.msdn.microsoft.com/.../threads. I don't personally get involved in much troubleshooting, so I'm not the best guy for that.


-Doug


Pingbacks and trackbacks (4)+