Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2000 to SQL Server 2008 DTS Package Conversion Runtime Issue


By:   |   Updated: 2010-11-15   |   Comments (5)   |   Related: More > Integration Services Upgrade

Problem

When converting DTS packages from SQL Server 2000 to SQL Server 2008 you may encounter this error message: "The Package Migration Wizard requires the SQL Server 2000 Data Transformation Services (DTS) runtime. To install the DTS runtime, run the SQL Server Installation Wizard. On the Components to Install page, click Advanced, then select Legacy Components for installation." The first thought is to run the SQL Server setup process, but after you go through the initial setup screens you find out that there is no Advanced page, so how do you do this?

Solution

When converting DTS packages from SQL Server 2000 to SQL Server 2008, you might get this error message when you try to use the "Migrate DTS 2000 Package" option within BIDS.

 converting DTS packages from SQL Server 2000 to SQL Server 2008

As I mentioned above, if you run through the SQL Server setup process, you won't find what you are looking for. So how do you get past this error?

What you need to do is download the "Run-time Support for DTS Packages" from the Microsoft site. This can be found on this page: Microsoft SQL Server 2008 Feature Pack page. You then need to scroll down until you find the "Microsoft SQL Server 2005 Backward Compatibility Components". Yes even though it says SQL Server 2005, this does work for SQL Server 2008 and SQL Server 2008 R2. Once there you need to select the correct package either for X86 (32 bit), X64 (64 bit) or IA64 (Itanium 64 bit).

After you download the MSI you will need to run the install. Just double click on the MSI file and go through the screens. If you only want to convert DTS packages to SSIS packages you only need to select the first option as shown below. If you want to do additional tasks, you can install the other components as needed.

even though it says SQL Server 2005, this does work for SQL Server 2008 and SQL Server 2008 R2

Once this has been installed you will be able to use the "Migrate DTS 2000 Package" feature within BIDS.

Next Steps


Last Updated: 2010-11-15


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, June 28, 2013 - 11:25:55 AM - Greg Robidoux Back To Top

@Robin - try this link

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_DTS.msi


Friday, June 28, 2013 - 9:13:59 AM - Robin Back To Top

Hi Greg,

I've just clicked on the link  in your solution paragraph then I 've had this message :
We are sorry, the page that you requested cannot be found.
The URL may have been misspelled or the page that you're looking may no longer be available.
Is there another solution ?
 
Bye.
Robin
 
 

Friday, April 19, 2013 - 10:01:05 AM - samarth Back To Top

Dts designer 2000 in sql 2008 r2‏

Able to open and migrated in sql2008 r2 - x64 thru designer 2000 but during first time when we open it gives SQLDMO.dll has not registered and when we click ok able to get thru.
Wants to make sure its something needs to conceren or fix


Wednesday, February 13, 2013 - 7:29:40 AM - satheesh Back To Top

Hello Sir,

     Thank you so much for the article. It helps a lot. For sql server 2008 the backward compatibility for 2005 works fine. Salute you for this article sir.

 

 


Tuesday, November 16, 2010 - 3:34:38 PM - Mark Shay Back To Top

Thanks for the Post.. We come across this issue fairly frequently in our environment.. in addition, to the software you mention there are additional steps we need to get DTS to work. See below:

1. Installed SQL Server 2005 Backward compatibility and SQL Server 2000 DTS Designer

http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_DTS.msi

 

2. Change the Path under environment variables to make the SQL Server 2000 DTS Path comes first before SQL Server 2008.

 

3. Copy the DLL and RLL files which are specified in below link.

http://msdn.microsoft.com/en-us/library/ms143755.aspx

To ensure that the DTS designer can be used in SQL Server Management Studio

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

2.       Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, 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. For example, for U.S. English, the lang_id subfolder will be "1033".

To ensure that the DTS designer can be used in Business Intelligence Development Studio

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

2.       Copy the files, SEMSFC.RLL, SQLGUI.RLL, and SQLSVC.RLL, 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. For example, for U.S. English, the lang_id subfolder will be "1033".

4. Server reboot

 


Learn more about SQL Server tools